Re: Unique Constraint with Multiple NULLS

Rather than create two identical tables you could just replicate the constrained columns and enforce uniqueness through a nullable foreign key constraint. Example:

CREATE TABLE foo1 (prikey INTEGER, fookey1 INTEGER, fookey2 INTEGER, UNIQUE (fookey1,fookey2), PRIMARY KEY (prikey,fookey1,fookey2))

CREATE TABLE foo2 (prikey INTEGER, fookey1 INTEGER, fookey3 INTEGER, UNIQUE (fookey1,fookey3), PRIMARY KEY (prikey,fookey1,fookey3))

CREATE TABLE Sometable (prikey INTEGER PRIMARY KEY, fookey1 INTEGER NULL, fookey2 INTEGER NULL, fookey3 INTEGER NULL, FOREIGN KEY (prikey,fookey1,fookey2) REFERENCES foo1 (prikey,fookey1,fookey2), FOREIGN KEY (prikey,fookey1,fookey3) REFERENCES foo2 (prikey,fookey1,fookey3) /* , ... other columns */)

INSERT INTO foo1 VALUES (100,1,2)
INSERT INTO foo1 VALUES (101,1,3)

INSERT INTO Sometable VALUES (100,1,2,NULL) INSERT INTO Sometable VALUES (101,1,3,NULL)

-- 
David Portas
------------
Please reply only to the newsgroup
--