Advanced Database Constraints: There Can Be Only One

One of the powers of RDBMS as we use them today are constraints. I use unique indexes, not null constraints and foreign keys on a regular basis and if you do any work with RDBMSs you probably do as well.

From time to time one comes at a point where you think “it would be nice to enforce this with some kind of constraint”, but you can’t. Well maybe you just didn’t try hard enough. This is the first in a little series of articles pointing out some tricks you can do with database constraints.

I’ll talk Oracle here since that is what I know best, but many of these things are possible in other systems as well.

Lets assume we have a person table and a hobby table which holds the hobbies of a person:

    CREATE TABLE person (
      id number PRIMARY KEY NOT NULL,
      name varchar2(200) NOT NULL
      );
     
    CREATE TABLE hobby (
      id number PRIMARY KEY NOT NULL,
      person_id number REFERENCES person NOT NULL,
      name varchar2(200) NOT NULL
      )

Just ignore that the hobby table probably needs some normalization. Now assume you want to flag the favorite hobby of a person and of course there can be only one favorite hobby per person.

You could create an additional table holding that information, linking person entries and hobby entries, with a unique constraint on the person_id. From a theoretical point of view this is the correct normalized way to do it. But it makes you add a complete table when you just need a flag. There is a somewhat denormalized way to do it, which is in cases like this one simpler to use.

    ALTER TABLE hobby
    ADD  is_favorite varchar2(1) UNIQUE;
     
    ALTER TABLE hobby ADD constraint check_is_favorite
    CHECK (is_favorite = 'Y');

The idea is to add a is_favorite column and make it unique, but also only allow a single value different from null using a check constraint. Since null values don’t get indexed you can have as many null values in a unique column as you want. This does look probmising, but there is a problem. The is_favorite column should only be unique per person, so we have to include the person_id. But now all the null values get indexed, since a unique key only ignores null values when all indexed columns are null. So we have to replace the null values with something unique. We can do that by creating a function based column which is identical to is_favorite for not null values and replaces the null values with the primary key (which of course is unique by definition). 

    ALTER TABLE hobby
    ADD is_favorite_unique AS (nvl(is_favorite, id));
     
    ALTER TABLE hobby ADD CONSTRAINT unique_favorite_hobby
    UNIQUE(person_id, is_favorite_unique);

If you remove the unique constraint from the is_favorite column you finally have the desired behavior:

    CREATE TABLE person (
      id number PRIMARY KEY NOT NULL,
      name varchar2(200) NOT NULL
      );
     
    CREATE TABLE hobby (
      id number PRIMARY KEY NOT NULL,
      person_id number REFERENCES person NOT NULL,
      name varchar2(200) NOT NULL
      );
     
    ALTER TABLE hobby
    ADD  is_favorite varchar2(1);
     
    ALTER TABLE hobby ADD constraint check_is_favorite
    CHECK (is_favorite = 'Y');
     
    ALTER TABLE hobby
    ADD is_favorite_unique AS (nvl(is_favorite, id));
     
    ALTER TABLE hobby ADD CONSTRAINT unique_favorite_hobby
    UNIQUE(person_id, is_favorite_unique);
     
    INSERT INTO person VALUES (1, 'Jens');
    INSERT INTO person VALUES (2, 'Alfred');
    INSERT INTO hobby (id, person_id, name, is_favorite) VALUES (10,1, 'coding', 'Y');
    INSERT INTO hobby (id, person_id, name, is_favorite) VALUES (20,2, 'soccer', NULL);
    INSERT INTO hobby (id, person_id, name, is_favorite) VALUES (21,2, 'reading', NULL);
    INSERT INTO hobby (id, person_id, name, is_favorite) VALUES (22,2, 'swimming', 'Y');
    – this fails with a unique exception
    – insert into hobby (id, person_id, name, is_favorite) values (23,2, 'watching highlander', 'Y');