On 2019-07-12 15:15, Scott Morgan blumf@...
[firebird-support] wrote:

> Are NULLs considered values for unique constraints on a table?
>
> e.g.
>
> CREATE TABLE blah (
> col1 INT NOT NULL,
> col2 INT,
>
> CONSTRAINT unq_blah UNIQUE(col1, col2)
> );
>
> INSERT INTO blah (col1, col2) (123, 321);
> INSERT INTO blah (col1, col2) (123, 999); /* okay */
> INSERT INTO blah (col1, col2) (123, 321); /* error */
>
> INSERT INTO blah (col1, col2) (123, NULL);
> INSERT INTO blah (col1, col2) (123, NULL); /* okay? error? */
>
> I'd like that NULL example to be an error, does the UNIQUE constraint
> do
> that, or do I have to use a CHECK constraint? If it does require the
> CHECK, are there any performance tips for that setup?

The second attempt to insert null will fail if the other column has the
same value. The behaviour of unique constraints and null is documented
in:

-
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-uqkey-nulls
-
https://firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/nullguide-keys.html#nullguide-keys-uk

Mark