Constraint Specifications
When a
constraint (see
Constraints) is added to a table, the table is checked to ensure that its contents do not violate the constraint. If the contents of the table do violate the constraint, the DBMS Server returns an error and does not add the constraint.
The following table summarizes the elements of the constraint_clause:
Named Constraints
If the constraint name is omitted, the DBMS Server assigns a name.
To assign a name to a constraint on the ALTER TABLE statement, use the following syntax:
ALTER TABLE tablename ADD CONSTRAINT constraint_name constraint_clause
constraint_name
Assigns a name to the constraint. It must be a valid object name. The keyword CONSTRAINT must be used only when specifying a name.
For example, the following statement adds a named constraint to the emp table:
ALTER TABLE emp ADD CONSTRAINT chksal CHECK(salary>0);
The following statement adds an internally named constraint to the emp table:
ALTER TABLE emp ADD CHECK (age>0);
To drop a constraint, using the following syntax:
ALTER TABLE tablename DROP CONSTRAINT constraint_name RESTRICT|CASCADE
For example, the following ALTER TABLE statement drops the constraint named chksal:
ALTER TABLE emp DROP CONSTRAINT chksal RESTRICT;
To find a system-defined constraint name, select the name from the iiconstraints system catalog:
SELECT * FROM iiconstraints WHERE tablename = tablename;
If a system-defined constraint name is being dropped, specify the constraint name using a delimited identifier (that is, in double quotes), because system-defined constraint names include special characters.
If a unique constraint upon which referential constraints depend is dropped, the dependent constraints are automatically dropped (unless restrict is specified). For example, given the following tables and constraints:
CREATE TABLE dept (dname CHAR(10) NOT NULL UNIQUE,
...);
CREATE TABLE emp (ename CHAR(10),
dname CHAR(10)
REFERENCES dept(dname));
If the unique constraint on the dname column of the dept table is dropped, the referential constraint on the dname column of emp is dropped automatically.
Restrict and Cascade
When a constraint or a column is dropped, specify either the RESTRICT or CASCADE option:
RESTRICT
Does not drop the constraint or column if one or more objects exist that depend on it. For example:
• A view with reference to the column in the base table
• A check constraint on the column being dropped
• A secondary index defined with this column
CASCADE
Deletes all objects that depend on the dropped constraint or column.
For example, if a unique constraint upon which a referential constraint is dependent is dropped, the dependent constraints are dropped. If a column is dropped, all integrity constraints, grants, views, and indexes that depend on the column are dropped.
The user is not provided with information describing the dependent objects that are dropped.
Note: Procedures and rules dependent on dropped columns are not dropped; instead, an error is returned when the rule or procedure is executed.
For example, the following statements create two tables with referential constraint. The referential constraint ("empref") of the second table depends on the unique constraint ("dept_unique") of the first table:
CREATE TABLE dept (
name CHAR(10) NOT NULL,
location CHAR(20),
CONSTRAINT dept_unique UNIQUE(name)
WITH STRUCTURE=HASH);
CREATE TABLE emp (
name CHAR(10) NOT NULL,
salary DECIMAL(10,2),
dept CHAR(10)
CONSTRAINT empref REFERENCES dept(name));
If the dept_unique constraint is dropped, the RESTRICT and CASCADE clauses determine the outcome of the ALTER TABLE statement as follows:
ALTER TABLE dept DROP CONSTRAINT dept_unique RESTRICT;
returns an error, because there is a referential constraint that depends on dept_unique. However,
ALTER TABLE dept DROP CONSTRAINT dept_unique CASCADE;
deletes both the dept_unique constraint and the dependent empref constraint.