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

Le 11 mars 2012 13:25, John English <> a écrit :
I have a table with a (possibly null) foreign key, and a non-null value:

   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 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?


 John English | My old University of Brighton home page is still here: