John English <[email protected]> writes: > 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),
Please file a bug report so that we can fix the manual. Derby has supported UNIQUE constraints on nullable columns since 10.4. > 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. That's the intended behaviour. http://db.apache.org/derby/docs/10.8/ref/rrefsqlj13590.html ,---- | A UNIQUE constraint defines a set of columns that uniquely identify | rows in a table only if all the key values are not NULL. If one or | more key parts are NULL, duplicate keys are allowed. `---- If I remember correctly, the semantics were chosen after careful examination of the SQL standard. > 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? Derby also has unique indexes that, in contrast to unique constraints, consider two null values duplicates: CREATE UNIQUE INDEX FOO_3 ON foo(section,title) -- Knut Anders
