Subject | Re: nulls in unique constraints |
---|---|
Author | Adam |
Post date | 2006-06-30T05:46:39Z |
Answering his own question:
create table test
(
id integer not null,
a integer not null,
b integer not null,
c integer,
CONSTRAINT PK_test PRIMARY KEY (ID),
CONSTRAINT UQ_test UNIQUE (a,b,c)
);
commit;
INSERT INTO TEST (ID, A, B, C) VALUES (1, 1, 1, NULL);
-- works to here
INSERT INTO TEST (ID, A, B, C) VALUES (2, 1, 1, NULL);
-- above line violates unique constraint.
So:
would therefore violate the unique constraint across (A,B,C)
Adam
create table test
(
id integer not null,
a integer not null,
b integer not null,
c integer,
CONSTRAINT PK_test PRIMARY KEY (ID),
CONSTRAINT UQ_test UNIQUE (a,b,c)
);
commit;
INSERT INTO TEST (ID, A, B, C) VALUES (1, 1, 1, NULL);
-- works to here
INSERT INTO TEST (ID, A, B, C) VALUES (2, 1, 1, NULL);
-- above line violates unique constraint.
So:
> One of the three fields can be null, the other two can not be null.Yes
> Will Firebird accept the unique constraint:
> WouldNo. In this case NULL = NULL is assumed to be true and these records
>
> ID A B C
> 1 1 1 null
> 2 1 1 null
>
> be considered unique?
would therefore violate the unique constraint across (A,B,C)
Adam