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.
Renaming a Column: the TO Clause
Changing the Data Type of a Column: the TYPE Clause
Changing the Position of a Column: the POSITION Clause
The DROP DEFAULT and SET DEFAULT Clauses
The SET NOT NULL and DROP NOT NULL Clauses
The COMPUTED [BY] or GENERATED ALWAYS AS Clauses
USING
ClauseThe 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
UNIQUE
ConstraintThe 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 KeysFirebird’s SQL-compliant rules for UNIQUE
constraints allow one or more NULL
s 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.
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