外键约束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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26524307/viewspace-1061280/