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

Create a free website or blog at WordPress.com.