FirebirdSQL logo
 DOMAININDEX 
NULL in Unique Keys

Firebird’s SQL-compliant rules for UNIQUE constraints allow one or more NULLs in a column with a UNIQUE constraint.This makes it possible to define a UNIQUE constraint on a column that does not have the NOT NULL constraint.

For UNIQUE keys that span multiple columns, the logic is a little complicated:

  • Multiple rows having null in all the columns of the key are allowed

  • Multiple rows having keys with different combinations of nulls and non-null values are allowed

  • Multiple rows having the same key columns null and the rest filled with non-null values are allowed, provided the non-null values differ in at least one column

  • Multiple rows having the same key columns null and the rest filled with non-null values that are the same in every column will violate the constraint

The rules for uniqueness can be summarised thus:

In principle, all nulls are considered distinct. However, if two rows have exactly the same key columns filled with non-null values, the NULL columns are ignored and the uniqueness is determined on the non-null columns as though they constituted the entire key.
Illustration
RECREATE TABLE t( x int, y int, z int, unique(x,y,z));
INSERT INTO t values( NULL, 1, 1 );
INSERT INTO t values( NULL, NULL, 1 );
INSERT INTO t values( NULL, NULL, NULL );
INSERT INTO t values( NULL, NULL, NULL ); -- Permitted
INSERT INTO t values( NULL, NULL, 1 );    -- Not permitted

FOREIGN KEY

A foreign key ensures that the participating column(s) can contain only values that also exist in the referenced column(s) in the master table.These referenced columns are often called target columns.They must be the primary key or a unique key in the target table.They need not have a NOT NULL constraint defined on them although, if they are the primary key, they will, of course, have that constraint.

The foreign key columns in the referencing table itself do not require a NOT NULL constraint.

A single-column foreign key can be defined in the column declaration, using the keyword REFERENCES:

... ,
  ARTIFACT_ID INTEGER REFERENCES COLLECTION (ARTIFACT_ID),

The column ARTIFACT_ID in the example references a column of the same name in the table COLLECTIONS.

Both single-column and multi-column foreign keys can be defined at the table level.For a multi-column foreign key, the table-level declaration is the only option.

...
  CONSTRAINT FK_ARTSOURCE FOREIGN KEY(DEALER_ID, COUNTRY)
    REFERENCES DEALER (DEALER_ID, COUNTRY),

Notice that the column names in the referenced (“master”) table may differ from those in the foreign key.

Note

If no target columns are specified, the foreign key automatically references the target table’s primary key.