db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jean-Yves Linet <jy.li...@gmail.com>
Subject Re: Unique constraint, or something like it
Date Sun, 11 Mar 2012 17:46:15 GMT
Can't you use 0 rather than 1 for "null" section.
May be your code changes are not so important, as when an integer value is
used in this context it is rare to use 0 as a significant value :
1- for insert just change default null by default 0
2- for select ResultSet.getInt return 0 for null value as the return type
is int and not Integer.
3- then you have to change your where clauses by ... section = 0 instead of
section IS NULL
JY

Le 11 mars 2012 13:25, John English <john.foreign@gmail.com> a écrit :

> 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/<http://www.cem.brighton.ac.uk/staff/je/>
> ------------------------------**------------------------------**
> ------------
>

Mime
View raw message