db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Unique constraint, or something like it
Date Mon, 12 Mar 2012 13:04:53 GMT
John English <john.foreign@gmail.com> 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

Mime
View raw message