Database Reference
In-Depth Information
You can also specify the KEEP or DROP INDEX options when drop-
ping primary and foreign key constraints. The KEEP INDEX option
retains index files for the constraints while still removing the constraints
from the tables. The default behavior is to drop the index when the primary
key is dropped.
20.3.5.1
Dropping Constraints with CASCADE
Both tables and constraints can be dropped with the CASCADE clause.
With respect to Referential Integrity, the term CASCADE implies that
when one constraint is dropped, any dependent constraints will be
dropped as well. Cascading generally applies a domino effect of dropping
any foreign key constraints, referentially related to the constraint of the
table being dropped. For example, dropping the primary key on the ART-
ISTS table with the CASCADE clause also drops the ARTIST_ID foreign
key constraint on the SONGS table. The result is that the ARTIST_ID
column and its data still exist in the SONGS table, but the foreign key
constraint is gone.
We can demonstrate this using the ARTISTS and SONGS tables. First,
we drop and re-create the ARTISTS and SONGS table to start with a fresh
copy of the tables.
DROP TABLE SONGS;
DROP TABLE ARTISTS;
CREATE TABLE ARTISTS(ARTIST_ID NUMBER PRIMARY KEY
, NAME VARCHAR2(32) NOT NULL UNIQUE);
CREATE TABLE SONGS(SONG_ID NUMBER PRIMARY KEY
, ARTIST_ID NUMBER NOT NULL
REFERENCES ARTISTS (ARTIST_ID)
, TITLE VARCHAR2(64) NOT NULL UNIQUE);
The following query shows resulting constraint details as shown in Fig-
ure 20.10.
SELECT TABLE_NAME "Table"
,DECODE(CONSTRAINT_TYPE,'P','Primary'
,'R','Foreign','') "Key"
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('ARTISTS','SONGS')
AND CONSTRAINT_TYPE IN ('P','R');
