|
|
|
When a constraint 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:
Type |
Keyword |
Example |
|---|---|---|
referential |
REFERENCES |
alter table dept add constraint chkmgr foreign key(mgr) references emp(ename) on delete set null; |
unique |
UNIQUE |
alter table emp add unique (eno, ename); |
check |
CHECK |
alter table emp add check (salary>0); |
primary key |
PRIMARY KEY |
alter table emp add constraint ekey primary key(eno); |
|
|
|
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
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.
|
|
|
When a constraint or a column is dropped, specify either the RESTRICT or CASCADE option:
Does not drop the constraint or column if one or more objects exist that depend on it. For example:
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 of the second table depends on the unique constraint of the first table:
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.
|
|
|
In an embedded ALTER TABLE statement, specify the WITH clause using a host string variable (WITH :hostvar).
|
|
|
Only the owner of a table can add or drop constraints or columns for that table. To define a referential constraint that refers to a table owned by another user, you must have the references privilege for the columns to which the constraint refers.
|
|
|
The ALTER TABLE statement acquires an exclusive lock on the table at the start of execution. The lock is held until the end of the transaction.
|
|
|