FirebirdSQL logo

Constraints

Five types of constraints can be specified.They are:

  • Primary key (PRIMARY KEY)

  • Unique key (UNIQUE)

  • Foreign key (REFERENCES)

  • CHECK constraint (CHECK)

  • NOT NULL constraint (NOT NULL)

Constraints can be specified at column level (“column constraints”) or at table level (“table constraints”).Table-level constraints are required when keys (unique constraint, primary key, foreign key) consist of multiple columns and when a CHECK constraint involves other columns in the row besides the column being defined.The NOT NULL constraint can only be specified as a column constraint.Syntax for some types of constraint may differ slightly according to whether the constraint is defined at the column or table level.

  • A column-level constraint is specified during a column definition, after all column attributes except COLLATION are specified, and can involve only the column specified in that definition

  • A table-level constraints can only be specified after the definitions of the columns used in the constraint.

  • Table-level constraints are a more flexible way to set constraints, since they can cater for constraints involving multiple columns

  • You can mix column-level and table-level constraints in the same CREATE TABLE statement

The system automatically creates the corresponding index for a primary key (PRIMARY KEY), a unique key (UNIQUE), and a foreign key (REFERENCES for a column-level constraint, FOREIGN KEY REFERENCES for table-level).

Names for Constraints and Their Indexes

Constraints and their indexes are named automatically if no name was specified using the CONSTRAINT clause:

  • The constraint name has the form INTEG_n, where n represents one or more digits

  • The index name has the form RDB$PRIMARYn (for a primary key index), RDB$FOREIGNn (for a foreign key index) or RDB$n (for a unique key index).