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 table_name 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 table_name 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 table_name = table_name;
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.