Subject Re: nulls in unique constraints
Author Adam
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:

> One of the three fields can be null, the other two can not be null.
> Will Firebird accept the unique constraint:

Yes

> Would
>
> ID A B C
> 1 1 1 null
> 2 1 1 null
>
> be considered unique?

No. In this case NULL = NULL is assumed to be true and these records
would therefore violate the unique constraint across (A,B,C)

Adam