"Ann W. Harrison" <aharrison@...> wrote:

>
> Interesting. I wonder what Adam was testing with that
> he got those odd results.

Why odd? The results are pretty expected.

> CONSTRAINT UQ_test UNIQUE (a,b,c)
> INSERT INTO TEST (ID, A, B, C) VALUES (1, 1, 1, NULL);
> INSERT INTO TEST (ID, A, B, C) VALUES (2, 1, 1, NULL);
>
> -- above line violates unique constraint.

In FB1, he cannot create an unique constraint on nullable fields at all. In
FB 1.5 and 2.0, it's possible, but two sets {1, 1, NULL} and {1, 1, NULL}
are considered not distinct, so the second insert violate the constraint.
And this perfectly matches the SQL spec.


Dmitry