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

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" <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
>
> ************
>

### 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