On Fri, 10 Sep 1999, omid omoomi wrote: > hi evey body, > I've some comments : > 1) I believe that there are some problems in table and db design and you are > doing the job in its hard way! There seems to be no primarry key defined on > the table. you can compose it at the end of your DDL(create table) > statement,like this : > primary key ( level1,level2,level3,level4 ) > this would make so many things be handeled transparently by the database( > i.e indexing and ...) . are yes, but that would still fail not work in my case. unless... > 2) Try to define some default values for each of columns again at your DDL > statements. This will avoid null values from being assigned to the columns.( > important: let it be different for each column,i.e 0 for leve1,1 for > level2,2for level3,3 for level4, other wise you may face some problems with > dupplication errors! ) Precicely, this seems to be the general concensus, I need to have some non-NULL empty value token. Thanks to everybody who e-mailed help/suggestions. regards, S. > > >From: "Jackson, DeJuan" <[EMAIL PROTECTED]> > >To: Stuart Rison <[EMAIL PROTECTED]>, pgsql-general > ><[EMAIL PROTECTED]>, pgsql-sql <[EMAIL PROTECTED]> > >Subject: RE: [GENERAL][SQL] Getting multiple field unique index to disting > >uish NULLs. > >Date: Fri, 10 Sep 1999 10:44:38 -0500 > > > >Use 0 or -1 instead of NULL. > > DEJ > > > > > -----Original Message----- > > > From: Stuart Rison [SMTP:[EMAIL PROTECTED]] > > > Sent: Friday, September 10, 1999 8:02 AM > > > To: pgsql-general; pgsql-sql > > > Subject: [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: [EMAIL PROTECTED] > > > > > > > > > > > > ************ > > > >************ > > > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > > ************ > ### 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: [EMAIL PROTECTED] ************
