主子表drop table constraints cascade的測試

######建立主表及子表

SQL> create table t_parent(a int primary key,b int);--採用內建約束方式

Table created.

SQL> create table t_child(a int primary key,parent int constraint fk_t_child references t_parent(a));##注意建立外來鍵約束的語法

Table created.

SQL> select table_name,constraint_name,status from user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                STATUS
------------------------------ ------------------------------ --------
T_CHILD                        FK_T_CHILD                     ENABLED ##子表的外來鍵約束

T_PARENT                       SYS_C006006                    ENABLED ##主表的pk約束
T_CHILD                        SYS_C006007                    ENABLED ##子表的pk約束

 

SQL> select index_name,table_name from user_indexes;  ###檢視建立約束相關索引的情況

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
SYS_C006007                    T_CHILD
SYS_C006006                    T_PARENT

 

 

SQL> drop table t_parent cascade constraints;###刪除主表加上cascade constraints

Table dropped.

SQL> select index_name,table_name from user_indexes;###主表的pk約束對應的索引沒有了

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
SYS_C006007                    T_CHILD

SQL> select table_name,constraint_name,status from user_constraints;###主子表的約束(pk)，但外來鍵約束沒有了

TABLE_NAME                     CONSTRAINT_NAME                STATUS
------------------------------ ------------------------------ --------
T_CHILD                        SYS_C006007                    ENABLED
BIN$i21D732Aid/gQAB/AQAJZA==$0 BIN$i21D731+id/gQAB/AQAJZA==$0 ENABLED

6 rows selected.

SQL>