Thread

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

Jackson, DeJuan <djackson@cpsgroup.com> — 1999-09-10T15:44:38Z

Use 0 or -1 instead of NULL.
	DEJ

> -----Original Message-----
> From:	Stuart Rison [SMTP:rison@biochemistry.ucl.ac.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.rison@biochem.ucl.ac.uk
> 
> 
> 
> ************