I have a table with a (possibly null) foreign key, and a non-null value:
CREATE TABLE foo (
section INTEGER DEFAULT NULL,
title VARCHAR(100) NOT NULL,
...
CONSTRAINT foo_1 FOREIGN KEY(section) REFERENCES bar(section)
ON DELETE SET NULL
);
I want the combination of section & title to be unique, including when
section is null. I tried this:
CONSTRAINT foo_2 UNIQUE(section,title)
which according to the manual should be illegal (UNIQUE should only be
OK if the columns are NOT NULL), but in 10.8.1.2 it works as long as the
section is not actually null; when the section is null it allows you
to insert duplicate titles.
I tried a CHECK constraint but this needs a select subquery which isn't
allowed. I can't think of another way to do this other than adding a
dummy section 1, then changing all my code to exclude it when I select
sections (which really doesn't appeal to me).
Can anyone think of another way to do this?
TIA,
------------------------------------------------------------------------
John English | My old University of Brighton home page is still here:
| http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------
