Table-level and Column-level Constraints
Constraints can be specified for groups of columns as part of the table definition (table-level constraints) or for individual columns as part of the column specification (column-level constraints).
The constraint has the following syntax:
[CONSTRAINT constraint_name] constraint
constraint_name
Defines a name for the constraint. If the name is omitted, the DBMS Server assigns one. The constraint name is used when dropping the constraint using the ALTER TABLE statement.
Note: We recommend defining a name when creating a constraint; otherwise system catalogs must be queried to determine the system-defined name.
constraint
Is either a table-level constraint (table_constraint) or a column-level constraint (column_constraint).
table_constraint is one or more of the following:
UNIQUE (column_name {, column_name})
PRIMARY KEY (column_name {, column_name})
REFERENCES [schema.]table_name [(column_name {, column_name})] [enforce_option] [referential_actions]
column_constraint is one or more of the following:
UNIQUE
PRIMARY KEY
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]table_name[(column_name)] [enforce_option] [referential_actions]
where:
enforce_option
Specifies whether constraints are enforced. Valid values are:
NOT ENFORCED
Does not enforce the constraint when it is defined or when the table is updated. The constraint is defined in the database catalogs. NOT ENFORCED constraints can be used to generate improved SQL statements or query plans.
Indexes are not created for NOT ENFORCED constraints.
ENFORCED
(Default) Enforces the constraint.
referential_actions
Defines alternate processing options if a referenced row is deleted or if referenced columns are updated when there are existing matching rows. A referential action specifies either an update rule or a delete rule, or both, in either sequence.
The ON UPDATE and ON DELETE rules have the following syntax:
ON UPDATE {RESTRICT | NO ACTION}
or
ON DELETE {RESTRICT | NO ACTION}
• NO ACTION - (Default) Returns an error upon any attempt to delete or update a referenced row with matching referencing rows.
• RESTRICT - Behaves the same as NO ACTION, but returns a different error code.
Constraint Examples
Here is an example of column-level constraints:
CREATE TABLE mytable(name CHAR(10) NOT NULL,
id INTEGER REFERENCES idtable(id),
location CHAR(10) UNIQUE;
Note: Multiple column constraints are separated by a space.
Here is an example of table-level constraints:
CREATE TABLE yourtable(firstname CHAR(20) NOT NULL,
lastname CHAR(20) NOT NULL,
UNIQUE(firstname, lastname));