On Fri, May 25, 2007 at 09:16:25 +0300, Cariotoglou Mike wrote:
> > > Seems that there is a problem on unique key fields when null
> > > values are allowed
> > >
> > > CREATE TABLE z (
> > > id VARCHAR(32) NOT NULL,
> > > f1 VARCHAR(32) NOT NULL,
> > > f2 VARCHAR(20),
> > > PRIMARY KEY (id)
> > > );
> > > CREATE UNIQUE INDEX z_I1 ON z (f1, f2)
> > >
> > > insert into z values ('1', '1', null);
> > > insert into z values ('2', '1', null);
> > >
> this should give a unique constraint error, but does not.
>
> testing with sql server and oracle shows that they *will* give an
> error. in this case, NULL = NULL seems to be true....
>
> can this be fixed ? it is quite a deviation from standard behavior.
This page
http://www.sqlite.org/nulls.html
explains the matter, line "nulls are distinct in a UNIQUE column". It
says Oracle treats NULLs as distinct though.
--
Tomash Brechko
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
