RE: [GENERAL][SQL] Getting multiple field unique index to disting uish NULLs.

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 ...) .
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! )

I think that would make it work !
good luck
omid.

>From: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com>
>To: Stuart Rison <rison(at)biochemistry(dot)ucl(dot)ac(dot)uk>, pgsql-general
><pgsql-general(at)postgreSQL(dot)org>, pgsql-sql <pgsql-sql(at)postgreSQL(dot)org>
>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:rison(at)biochemistry(dot)ucl(dot)ac(dot)uk]
> > 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: s(dot)rison(at)biochem(dot)ucl(dot)ac(dot)uk
> >
> >
> >
> > ************
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com