Firebird Documentation Index → Firebird Null Guide → Keys and unique indices |
NULL
s are never allowed in primary keys. A column can only be (part
of) a PK if 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.
Firebird 1.5 has a bug that allows primary keys to be defined on a NOT
NULL column with NULL
entries. How these
NULL
s can exist in such a column will be explained later.
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 NULL
s or duplicate values, or
the creation will fail. Once the index is in place, insertion of NULL
s
or duplicate values is no longer possible.
In Firebird 1.5 and up, unique keys and unique indices allow
NULL
s, and what's more: they even allow multiple
NULL
s. With a single-column key or index, you can insert as many
NULL
s 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 as such impose no restrictions with respect to NULL
s.
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.
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 NULL
s are absolutely forbidden in the target key (for
instance if the target is a PK), the foreign key column may still contain
NULL
s, unless this is prevented by additional constraints.
Firebird Documentation Index → Firebird Null Guide → Keys and unique indices |