[GENERAL][SQL] Getting multiple field unique index to distinguish NULLs.
От | Stuart Rison |
---|---|
Тема | [GENERAL][SQL] Getting multiple field unique index to distinguish NULLs. |
Дата | |
Msg-id | Pine.LNX.4.10.9909101349380.520-100000@bsmlx17 обсуждение исходный текст |
Ответы |
create function(date,date,date,...)
|
Список | pgsql-sql |
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.rison@biochem.ucl.ac.uk
В списке pgsql-sql по дате отправления: