Keeping it Small and Simple

2007.05.23

Multi-column constraints in SQL

Filed under: SQL — Lorenzo E. Danielsson @ 02:19

It is quite common that programmers learn only as much SQL as they need. I belong to that group. So it was only recently I discovered multi-column constraints, and only because I needed them for a project I was working on.

Let’s say that we have a table called categories. Let’s further assume that categories are hierarchical, that is, a category can have “child categories”. A simple table definition (for MySQL) could look as follows:

create table categories(
  id int primary key auto_increment,
  name varchar(100) unique,
  parent int
);

In the example above I have added a unique constraint to the name column. But let us suppose that I don’t want a name to totally unique. I just want to make sure that it is unique within a particular category. Let me illustrate this.

Suppose I have two categories, Math and Physics. Each one should be able to contain a sub-category named Introduction. But, neither of the two should be allowed to contain two sub-categories with the same name. So I cannot have Introduction appearing twice in the category Math.

If Math has the id 1 and Physics the id 2, then having the following should be valid:

id name parent
1 Math 0
2 Physics 0
3 Introduction 1
4 Introduction 2

The following, on the other hand would not be valid:

id name parent
1 Math 0
2 Physics 0
3 Introduction 1
4 Introduction 1

It is clear that what we need is a constraint that applies to two columns, so that the name and parent columns together must be unique. So we re-write the original table definition as follows:

create table categories(
  id int primary key auto_increment,
  name varchar(100),
  parent int,
  unique(name, parent)
);

That is all there is to it. We can have multiple rows with the same parent and we can have multiple rows with the same name. What we cannot have is multiple rows with the same name and parent. Simple, isn’t it?

Advertisements

3 Comments »

  1. WOW! I never knew about that! That’s really great. Thanks for the nice concise post!

    Comment by Jeremy Schneider — 2007.07.10 @ 19:01

  2. @Jeremy: “WOW!” is exactly what I felt when I discovered it as well..

    It’s interesting that no matter how much you think you know (and I admit to, at times, thinking a know quite a bit), there’s still a lot to learn. 🙂

    Comment by Lorenzo E. Danielsson — 2007.07.30 @ 03:51

  3. THANK YOU! I’m in the same boat. I only learn as much SQL as I need and I suddenly NEEDED this. Thanks very much!

    Comment by T.R. — 2007.10.16 @ 19:39


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: