How to Declare Constraints

Go Up to Defining Integrity Constraints on a Table

When declaring a table-level or a column-level constraint, you can optionally name the constraint using the CONSTRAINT clause. If you omit the ­CONSTRAINT clause, InterBase generates a unique system constraint name which is stored in the RDB$RELATION_CONSTRAINTS system table.

Tip: To ensure that the constraint names are visible in ­RDB$RELATION_CONSTRAINTS, commit your transaction before trying to view the constraint in the RDB$RELATION_CONSTRAINTS system table.

The syntax for a column-level constraint is:

<col_constraint> = [CONSTRAINT constraint] <constraint_def>
 [<col_constraint> ...]
<constraint_def> =
UNIQUE | PRIMARY KEY
 | CHECK (<search_condition>)

 | REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

The syntax for a table-level constraint is:

<tconstraint> = [CONSTRAINT constraint] <tconstraint_def>
 [<tconstraint> ...]
<tconstraint_def> = {PRIMARY KEY | UNIQUE} (col [, col …])
 | FOREIGN KEY (col [, col …])
 REFERENCES other_table [(other_col [, other_col …])]
 [ON DELETE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
 | CHECK (<search_condition>)

Tip: Although naming a constraint is optional, assigning a descriptive name with the CONSTRAINT clause can make the constraint easier to find for changing or dropping, and easier to find when its name appears in a constraint violation error message.

The following statement illustrates how to create a simple, column-level ­PRIMARY KEY constraint:

CREATE TABLE COUNTRY
(COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
CURRENCY VARCHAR(10) NOT NULL);

The next example illustrates how to create a UNIQUE constraint at both the column level and the table level:

CREATE TABLE STOCK
 (MODEL SMALLINT NOT NULL UNIQUE,
 MODELNAME CHAR(10) NOT NULL,
 ITEMID INTEGER NOT NULL,
CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID));