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 Constraints
To ensure that no two rows have the same value in a particular column or set of columns, specify UNIQUE NOT NULL.
Note: If a column is specified as UNIQUE, NOT NULL must also be specified.
The following example of a column-level unique constraint ensures that no two departments have the same name:
create table dept (dname character(10)
not null unique, ...);
In the preceding example, the unique constraint ensures that no two departments have the same name.
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 depts (dname character(10) not null,
dlocation character(10) not null,
unique (dname, dlocation));
Notes:
• Any column or set of columns that is designated as the primary key is implicitly unique and must be specified as NOT NULL. A table can have only one primary key, but can have any number of unique constraints.
• Unique constraints may create system indexes that cannot be explicitly dropped by the table owner. These indexes are used to enforce the unique constraint.
CHECK Constraints
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 character(25), sal decimal
check (sal > 0));
The
expression (see
Expressions) specified in the check constraint must be a Boolean expression.
To specify a check constraint for a group of columns, the check constraint must be specified at the table level (rather than 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 character(10),
location character(10),
budget decimal,
expenses decimal,
check (budget > 0 and expenses <= budget));
Check constraints cannot include the following:
• Subqueries
• Set functions (aggregate functions)
• Dynamic parameters
• Host language variables
Column-level check constraints cannot refer to other columns.
Referential Constraints
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.] table_name (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.] table_name [(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.
Example
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.