8. SQL Statements : ALTER TABLE : Constraint Specifications : Restrict and Cascade
 
Share this page                  
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.