Drop table cascade constraints

SQL> create table t (id number,name varchar2(20));
Table created.
SQL> create table t1 (id number,sal number);

Table created.
SQL> alter table t add constraint t_pk primary key (id);
Table altered.
SQL> alter table t1 add constraint t_fk foreign key (id) references t (id);
Table altered.
SQL> insert into t values (1,#39;JACK');
1 row created.
SQL> insert into t values (2,#39;MARY');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> insert into t1 values (1,1000);
1 row created.
SQL> insert into t1 values (2,1500);
1 row created.
SQL> commit;
SQL> insert into t1 values (3,200);
insert into t1 values (3,200)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.T_FK) violated - parent key not found
(違反了constraint，員工基本資料表根本沒有3號這個員工，何來的銷售紀錄。)

SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
(违反了constraint，员工销售表t1有參照到table t，这个reference relation不允许你drop table t)
SQL> drop table t cascade constraints;
Table dropped.
SQL> select * from t1;
ID SAL
---------- ----------
1 1000
2 1500
SQL> select CONSTRAINT_NAME,TABLE_NAME from dba_constraints where wner = #39;SYS' and TABLE_NAME = 'T1'
no rows selected
SQL>
我们可以发现利用Drop table cascade constraints可以以刪除关联table t的constraint來达成你drop table t的目的，原來属于t1的foreign key constraint已经跟随着被删除掉了，但是，储存在table t1的资料可不会被删除，也就是说Drop table cascade constraints 是不影响到存储于objec里的row data。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-670044/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-670044/