Constraints
To ensure that the contents of columns fulfill your database requirements, specify constraints.
Constraints are 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.
Note: Constraints are not checked when adding rows to a table using the COPY statement.
Constraints can be specified for individual columns or for the entire table. For details, see
Column-Level Constraints and Table-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.
• Check constraint – Ensures that the contents of a column fulfills user-specified criteria (for example, “salary >0”). Check constraints are specified using the CHECK 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.
Unique Constraint
To ensure that no two rows have the same value in a particular column or set of columns, specify UNIQUE.
Note: A column specified as UNIQUE cannot be nullable (NOT NULL is required.)
The following example of a column-level unique constraint ensures that no two departments have the same name:
CREATE TABLE dept(dname CHAR(10) UNIQUE NOT NULL, ...);
To ensure that the data in a group of columns is unique, specify the unique constraint at the table level (rather than for individual columns). A maximum of 32 columns can be specified in a table-level unique constraint.
The following example of a table-level unique constraint ensures that no two departments in the same location have the same name. The columns are declared not null, as required by the unique constraint:
CREATE TABLE project (
proj_id INT NOT NULL NOT DEFAULT,
proj_dept_id INT NOT NULL WITH DEFAULT,
proj_name CHAR(25) NOT NULL,
UNIQUE (proj_id) WITH STRUCTURE = HASH);
A table can have only one primary key, but can have any number of unique constraints. For example:
CREATE TABLE project (
proj_id INT NOT NULL NOT DEFAULT,
proj_dept_id INT NOT NULL WITH DEFAULT,
proj_name CHAR(25) NOT NULL UNIQUE,
UNIQUE (proj_dept_id) WITH STRUCTURE = HASH,
PRIMARY KEY (proj_id));
Note: Unique constraints create system indexes that cannot be explicitly dropped by the table owner. The indexes are used to enforce the unique constraint.
Check Constraint
To create conditions that a particular column or set of columns must fulfill, specify a check constraint using the CHECK option. For example, to ensure that salaries are positive numbers:
CREATE TABLE emps (name CHAR(25), sal MONEY,
CONSTRAINT check_salary CHECK (sal > 0));
The
expression (see
IS II_DATE_FORMAT Predicate) specified in the check constraint must be a Boolean expression.
To specify a check constraint for a group of columns, specify the check constraint at the table level (as opposed to specifying check constraints for individual columns).
The following example of a table-level check constraint ensures that each department has a budget and that expenses do not exceed the budget:
CREATE TABLE dept (dname CHAR(10),
location CHAR(10),
budget MONEY,
expenses MONEY,
CONSTRAINT check_amount CHECK (budget > 0 AND
expenses <= budget));
Note: The way nullability is specified for a column determines whether you can change the nullability of the column. If CHECK...NOT NULL is specified for a column, use the ALTER TABLE...DROP CONSTRAINT statement to remove the constraint (because the column is created as nullable-that is, with an additional byte for the null indicator-and the check constraint is used to prevent nulls from being inserted). However, if NOT NULL is specified (as opposed to a CHECK...is NOT NULL CONSTRAINT), the constraint cannot be removed using the ALTER TABLE...DROP CONSTRAINT statement because the column was created without the additional byte for the null indicator, and the additional byte cannot be added.
Check constraints cannot include the following:
• Subselects
• Set functions (aggregate functions)
• Dynamic parameters
• Host language variables
Column-level check constraints cannot reference other columns.
Referential Constraint
To validate an entry against the contents of a column in another table (or another column in the same table), specify a referential constraint using the REFERENCES option. The references option maintains the referential integrity of your tables.
The column-level referential constraint has the following syntax:
REFERENCES [schema.] tablename (column_name)[referential actions] [constraint_with_clause]
The following example of a column-level referential constraint ensures that no employee is assigned to a department that is not present in the dept table:
CREATE TABLE emp (ename CHAR(10),
edept CHAR(10) REFERENCES dept(dname));
The table-level referential constraint has the following syntax, including the FOREIGN KEY... REFERENCES option:
FOREIGN KEY (column_name{,column_name})
REFERENCES [schema.] tablename [(column_name{,column_name}][referential actions] [constraint_with_clause]
The following example of a table-level referential constraint verifies the contents of the name and empno columns against the corresponding columns in the emp table to ensure that anyone entered into the table of managers is on file as an employee:
CREATE TABLE mgr (name CHAR(10),
empno CHAR(5),
...
FOREIGN KEY (name, empno) REFERENCES emp);
The preceding example omits the names of the referenced column; the emp table must have a primary key constraint that specifies the corresponding name and employee number columns.
referential actions
Allow the definition of alternate processing options in the event a referenced row is deleted, or 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 {CASCADE | SET NULL | RESTRICT | NO ACTION}
or
ON DELETE {CASCADE | SET NULL | RESTRICT | NO ACTION}
ON UPDATE CASCADE
Causes the values of the updated referenced columns to be propagated to the referencing columns of the matching rows of the referencing table.
ON DELETE CASCADE
Specifies that if a delete is attempted on a referenced row that has matching referencing rows, the delete is “cascaded” to the referencing table as well. That is, the matching referencing rows are also deleted. If the referencing table is itself a referenced table in some other referential relationship, the delete rule for that relationship is applied, and so forth. (Because rule types can be mixed in a referential relationship hierarchy, the second delete rule can be different from the first delete rule.) If an error occurs somewhere down the line in a cascaded operation, the original delete fails, and no update is performed.
NO ACTION
Is the default behavior of returning 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. Both options are supported for ANSI SQL compliance.
SET NULL
Causes the referencing columns of the matching rows to be set to the null value (signifying that they do not currently participate in the referential relationship). The columns can be updated later to a non-null values, at which time the resulting row must find a match somewhere in the referenced table.
Here is an example of the delete and update rules:
CREATE TABLE employee (empl_no INT NOT NULL PRIMARY KEY)
emp_name CHAR(20) NOT NULL,
dept_id CHAR(6) REFERENCES department (dept_id)
ON DELETE CASCADE ON UPDATE CASCADE,
mgrno INT REFERENCES employee (empl_no) ON UPDATE CASCADE
ON DELETE SET NULL);
If a department row is deleted, all employees in that department are also deleted. If a department ID is changed in the department table, it is also changed in all referencing employee rows.
If a manager's ID is changed, his employees are changed to match. If the manager is fired, all his employees have mgr_id set to null.
The following considerations apply to the table and column being referenced (the column specified following the keyword references):
• The referenced table must be an existing base table (it cannot be a view).
• The data types of the columns must be comparable.
• You must have references privilege for the referenced columns.
• If the table and column names are specified, the referenced columns must compose a unique or primary key constraint for the referenced table.
• In a table-level referential constraint, if multiple columns are specified, the columns specified for the referencing table must correspond in number, data type, and position to the columns specified for the referenced table, and must compose a unique or primary key constraint for the referenced table.
• If the referenced table is specified and the column name is omitted, the referenced table must have a primary key constraint; the referencing columns are verified against the primary key of the referenced table.
Primary Key Constraint
The primary key constraint is used to denote one or more columns to which other tables refer in referential constraints. A table can have only one primary key; the primary key for a table is implicitly unique and must be declared not null.
This is an example of a primary key constraint and a related referential constraint:
Referenced table:
CREATE TABLE partnumbers(partno INT PRIMARY KEY...);
Referencing table:
CREATE TABLE inventory(ipartno INT...
FOREIGN KEY (ipartno) REFERENCES partnumbers);
In this case, the part numbers in the inventory table are checked against those in the partnumbers table; the referential constraint for the inventory table is a table-level constraint and therefore must specify the FOREIGN KEY clause. The referential constraint for the inventory does not specify the column that is referenced in the partnumbers table. By default, the DBMS checks the column declared as the primary key. For related details, see Referential Constraint.
Column-Level Constraints and Table-Level Constraints
Constraints can be specified for individual columns as part of the column specification (column-level constraints) or for groups of columns as part of the table definition (table-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 column-level constraint (column_constraint) or table-level constraint (table_constraint).
column_constraint is one or more of the following:
UNIQUE [WITH constraint_with_clause]
PRIMARY KEY [WITH constraint_with_clause]
REFERENCES [schema.]tablename[(column_name)] [enforce_option] [referential_actions]
[WITH constraint_with_clause]
table_constraint is one or more of the following:
UNIQUE (column_name {, column_name}) [WITH constraint_with_clause]
PRIMARY KEY (column_name {, column_name}) [WITH constraint_with_clause]
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]tablename [(column_name {, column_name})]
[enforce_option] [referential_actions]
[WITH constraint_with_clause]
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.
Examples
Here is an example of column-level constraints:
CREATE TABLE mytable(name CHAR(10) NOT NULL,
id INTEGER REFERENCES idtable(id),
age INTEGER CHECK (age > 0));
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));