Thread: Create Table Unique problem... bug?

Hi, I've a problem creating a table. My Sql
instruction is:

CREATE TABLE questiontypes (
  id integer NOT NULL,
  flags integer,
  label text,
  UNIQUE (id,flags)
);

Is it ok?? I must have 'flags' like to NULL... but
'id' is NOT NULL...
Now if I insert a row:

INSERT INTO questiontypes (id,flags,label) values
(2,null,'a');

and re-insert it... I can!!!! Now there are two
identical row, and UNIQUE??
I think postgres must occur an error? Is it Ok?

(The same problem is with:
INSERT INTO questiontypes (id,label) values (2,'a');
Is it ok?)

Thanks you, Mauro


______________________________________________________________________
Yahoo! Mail: 6MB di spazio gratuito, 30MB per i tuoi allegati, l'antivirus, il filtro Anti-spam
http://it.yahoo.com/mail_it/foot/?http://it.mail.yahoo.com/

Mauro Bertoli wrote:

>Hi, I've a problem creating a table. My Sql
>instruction is:
>
>CREATE TABLE questiontypes (
>  id integer NOT NULL,
>  flags integer,
>  label text,
>  UNIQUE (id,flags)
>);
>
>Is it ok?? I must have 'flags' like to NULL... but
>'id' is NOT NULL...
>Now if I insert a row:
>
>INSERT INTO questiontypes (id,flags,label) values
>(2,null,'a');
>
>and re-insert it... I can!!!! Now there are two
>identical row, and UNIQUE??
>I think postgres must occur an error? Is it Ok?
>
Hi Mauro,
yes, this is ok,
because NULL is not equal to NULL.
(In other words, every NULL is a Unique NULL).
Every comparison to NULL returns NULL.

Regards,
Dani

On Wed, May 28, 2003 at 15:34:41 +0200,
  Mauro Bertoli <bertolima@yahoo.it> wrote:
> INSERT INTO questiontypes (id,flags,label) values
> (2,null,'a');
>
> and re-insert it... I can!!!! Now there are two
> identical row, and UNIQUE??
> I think postgres must occur an error? Is it Ok?

This is how unique constraints work. Any null value is considered as
different from any other null value. If that isn't how you want things
to work, using some value other than null to represent whatever you
are using null to represent.

Am Mit, 2003-05-28 um 15.34 schrieb Mauro Bertoli:
> and re-insert it... I can!!!! Now there are two
> identical row, and UNIQUE??
> I think postgres must occur an error? Is it Ok?
>
> (The same problem is with:
> INSERT INTO questiontypes (id,label) values (2,'a');
> Is it ok?)

I don't know the answer to your question, but I think the problem could
be that you NULL compared with anything will always be undefined.

e.g. NULL=NULL is false

maybe: (NULL,'a')=(NULL,'a') is false, too ?

bye
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de