FirebirdSQL logo
 DOMAININDEX 
The USING Clause

The USING clause allows you to specify a user-defined name for the index that is created automatically and, optionally, to define the direction of the index — either ascending (the default) or descending.

PRIMARY KEY

The PRIMARY KEY constraint is built on one or more key columns, where each column has the NOT NULL constraint specified.The values across the key columns in any row must be unique.A table can have only one primary key.

  • A single-column primary key can be defined as a column-level or a table-level constraint

  • A multi-column primary key must be specified as a table-level constraint

The UNIQUE Constraint

The UNIQUE constraint defines the requirement of content uniqueness for the values in a key throughout the table.A table can contain any number of unique key constraints.

As with the primary key, the unique constraint can be multi-column.If so, it must be specified as a table-level constraint.

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