[GENERAL][SQL] Getting multiple field unique index to distinguish NULLs.

Dear All,

Consider the following table:

myscheme_id|level1|level2|level3|level4|function
-----------+------+------+------+------+-------------------------------
11| 4| 5| 1| 3|Long John Silver
12| 1242| 3| 44| 5|Metabolism
13| 1| 2| 3| 4|Transport
1| 1| 4| 3| |Energy
9| 1| 2| 3| 1|Signaling
3| 1| 2| 3| 2|test1
18| 1| 2| | |test2
19| 1| 2| | |test3
21| 1| 2| | |test4

This is essentially a hierarchical key set-up; each function can be
identified by a key (which can be thought of as the concatenation of
level1, level2, level3 and level4).

But you can add a function at any level (i.e. only level1 must be given)
so:

INSERT INTO myscheme_funcat (level1,function) VALUES (1,'Top level
function');

is OK.

I am trying to get the database to reject INSERTS for keys already
occupied

CREATE UNIQUE INDEX myscheme_idx ON myscheme_funcat
(level1,level2,level3,level4);

The problem is that the indexing considers all NULLs to be distinct thus:

INSERT INTO myscheme_funcat (level1,level2,level3,level4,function) VALUES
(4,5,76,NULL,'OK');
INSERT 1044737 1

but so does
INSERT INTO myscheme_funcat (level1,level2,level3,level4,function) VALUES
(4,5,76,NULL,'Should fail because position 4.5.76 already occupied');
INSERT 1044738 1

Works because 4,5,76,NULL is considered DISTINCT from 4,5,76,NULL (all
NULLs are different).

So, any ideas, workarounds etc.??

cheers,

S.

### Please Note New Details ###
Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: s(dot)rison(at)biochem(dot)ucl(dot)ac(dot)uk