外來鍵約束drop table cascade constraints

--整理以前的學習筆記

create table dept(deptno number(2) primary key,dname varchar2(20));
create table emp(empno number(4) primary key,ename varchar2(20),deptno number(2));
alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno);

SQL> select object_name,object_type from user_objects;
OBJECT_NAME                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
DEPT                                                                             TABLE
SYS_C00161025                                                                    INDEX
EMP                                                                              TABLE
SYS_C00161026                                                                    INDEX
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME
------------------------------ --------------- ------------------------------
SYS_C00161026                  P               EMP
EMP_DEPTNO_FK                  R               EMP
SYS_C00161025                  P               DEPT

insert into dept values(10,'Test');
insert into dept values(20,'Develop');
insert into dept values(30,'HR');
insert into dept values(40,'Sales');
insert into dept values(50,'Finance');
commit;
select * from dept;

insert into emp values(0001,'Nancy',10);
insert into emp values(0002,'Tom',10);
insert into emp values(0003,'Anne',20);
insert into emp values(0004,'Alice',30);
insert into emp values(0005,'Gaby',40);
insert into emp values(0006,'Lynette',30);
commit;
select * from emp;

alter table emp modify constraint emp_deptno_fk on delete set null;--好像沒有這樣的語法，需要先刪除約束再重

新建立
alter table emp drop constraint emp_deptno_fk;
alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete set

null;

delete from dept where deptno='10';
commit;

SQL> select * from emp;
 
EMPNO ENAME                DEPTNO
----- -------------------- ------
    1 Nancy               
    2 Tom                 
    3 Anne                     20
    4 Alice                    30
    5 Gaby                     40
    6 Lynette                  30
 
6 rows selected
 
SQL> select * from dept;
 
DEPTNO DNAME
------ --------------------
    20 Develop
    30 HR
    40 Sales
    50 Finance

alter table emp drop constraint emp_deptno_fk;
alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete cascade;

delete from dept where deptno='20';
commit;

SQL> select * from emp;
 
EMPNO ENAME                DEPTNO
----- -------------------- ------
    1 Nancy               
    2 Tom                 
    4 Alice                    30
    5 Gaby                     40
    6 Lynette                  30
 
SQL> select * from dept;
 
DEPTNO DNAME
------ --------------------
    30 HR
    40 Sales
    50 Finance

SQL> drop table dept;
 
drop table dept
 
ORA-02449: 表中的唯一/主鍵被外來鍵引用
SQL> truncate table dept;
 
truncate table dept
 
ORA-02266: 表中的唯一/主鍵被啟用的外來鍵引用
 
SQL> delete from emp;
 
5 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select * from emp;
 
EMPNO ENAME                DEPTNO
----- -------------------- ------
 
SQL> drop table dept;
 
drop table dept
 
ORA-02449: 表中的唯一/主鍵被外來鍵引用
SQL> truncate table dept;
 
truncate table dept
 
ORA-02266: 表中的唯一/主鍵被啟用的外來鍵引用

 

--dept作為外來鍵約束中的父表，即使子表是空的，也是不能刪除或截斷（truncate）父表的

 

SQL> drop table dept cascade constraints;
 
Table dropped

 

--cascade constraints會刪除這個外來鍵約束，但是drop table本身會刪除表和表上的索引

 

SQL> select object_name,original_name,type from recyclebin;
 
OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- -------------------------
BIN$bcICjAueQ/ekMLy2QJ6GGw==$0 SYS_C00161025                    INDEX
BIN$7FcUpfs4QvuWPLfdY20WXw==$0 DEPT                             TABLE
--這兩條記錄是drop table dept之後（刪除了表和表上的主鍵索引），放在recyclebin裡的

SQL> select constraint_name,constraint_type,table_name from user_constraints;
 
CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME
------------------------------ --------------- ------------------------------
SYS_C00161026                  P               EMP
BIN$MAHIsiW1Q82obz6WEOtqyw==$0 P               BIN$7FcUpfs4QvuWPLfdY20WXw==$0

由於建立主鍵約束之後，會同時建立一個同名的索引，但是表刪除之後recyclebin裡面只有索引，沒有約束，但是查詢

user_constraints表裡面的約束名和對應的表名已經發生變化了

SQL> select object_name,object_type from user_objects;
 
OBJECT_NAME                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
EMP                                                                              TABLE
SYS_C00161026                                                                    INDEX
--可以看到dept對應的表和索引已經在user_objects裡面查詢不到了

SQL> purge recyclebin;
 
Done
 
SQL> select constraint_name,constraint_type,table_name from user_constraints;
 
CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME
------------------------------ --------------- ------------------------------
SYS_C00161026                  P               EMP
 
SQL> select object_name,original_name,type from recyclebin;
 
OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- -------------------------

--purge recyclebin之後user_constraints裡面相應的約束（怪怪的名字的約束）也已經沒掉了

create table dept(deptno number(2) primary key,dname varchar2(20));
create table emp(empno number(4) primary key,ename varchar2(20),deptno number(2));
alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno);
如果drop的是子表（emp表），那麼直接drop table emp;就可以了，不用cascade constraints，也會把相應的表上的約束

和索引都刪除掉

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26524307/viewspace-1061280/，如需轉載，請註明出處，否則將追究法律責任。