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, the DBMS Server returns an error and aborts the statement. 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 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 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);

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. 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 specified in the check constraint must be a Boolean expression. For details about expressions, see the chapter "Understanding the Elements of SQL Statements."

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.] 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 nul),
        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 Constraints

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 Server checks the column declared as the primary key. For related details, see Referential Constraint.