Constraints
To ensure that the contents of columns fulfill your database requirements, specify constraints.
Ordinary (enforced) constraints are always checked at the end of every statement that modifies the table. If the constraint is violated, an error is returned and the statement is aborted. If the statement is within a multi-statement transaction, the transaction is not aborted.
Constraints can also be declared as NOT ENFORCED. This allows the database designer to describe a constraint (such as a referential relationship) without the overhead of checking the constraint. The assumption is that the constraint will be enforced externally in some way, and so the DBMS does not have to do it. The constraint description is available to the query optimizer and to external query generators, allowing better query plans or better queries to be generated. If the actual data violates the non-enforced constraint, incorrect results are possible.
Constraints can be specified for individual columns or for the entire table. For details, see
Table-level and Column-level Constraints.
The types of constraints are:
• Unique constraint – Ensures that a value appears in a column only once. Unique constraints are specified using the UNIQUE option.
• Referential constraint – Ensures that a value assigned to a column appears in a corresponding column in another table. Referential constraints are specified using the REFERENCES option.
• Primary key constraint – Declares one or more columns for use in referential constraints in other tables. Primary keys must be unique.
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));