Keys and unique indices

Primary keys

NULLs are never allowed in primary keys. A column can only be (part of) a PK it has been defined as NOT NULL, either in the column definition or in a domain definition. Note that a “CHECK (XXX IS NOT NULL)” constraint won't do: you need a NOT NULL specifier right after the data type.

Warning

Firebird 1.5 has a bug that allows primary keys to be defined on a NOT NULL column with NULL entries. How these NULLs can exist in such a column will be explained later.

Unique keys and indices

Firebird 1.0

In Firebird 1.0, unique keys are subject to the same restrictions as primary keys: the column(s) involved must be defined as NOT NULL. For unique indices, this is not necessary. However, when a unique index is created the table may not contain any NULLs or duplicate values, or the creation will fail. Once the index is in place, insertion of NULLs or duplicate values is no longer possible.

Firebird 1.5 and higher

In Firebird 1.5 and up, unique keys and unique indices allow NULLs, and what's more: they even allow multiple NULLs. With a single-column key or index, you can insert as many NULLs as you want in that column, but you can insert each non-NULL value only once.

If the key or index is defined on multiple columns in Firebird 1.5 and higher:

You can insert multiple rows where all the key columns are NULL;
But as soon as one or more key columns are non-NULL, each combination of non-NULL values must be unique in the table. Of course with the understanding that (1, NULL) is not the same as (NULL, 1).

Foreign keys

Foreign keys as such impose no restrictions with respect to NULLs. Foreign key columns must always reference a column (or set of columns) that is a primary key or a unique key. A unique index on the referenced column(s) is not enough.

Note

In versions up to and including 2.0, if you try to create a foreign key referencing a target that is neither a primary nor a unique key, Firebird complains that no unique index can been found on the target – even if such an index does exist. In 2.1, the message correctly states that no unique or primary key could be found.

Even if NULLs are absolutely forbidden in the target key (for instance if the target is a PK), the foreign key column may still contain NULLs, unless this is prevented by additional constraints.