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 <[email protected]> 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/> > ------------------------------**------------------------------** > ------------ >
