试验环境：
1）数据库版本：oracle 11.2.0.4
2）建表脚本：以scott的dept及emp表为基础。
父表：dept
|
-- Create table create table DEPT ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13) ) -- Create/Recreate primary, unique and foreign key constraints alter table DEPT add constraint PK_DEPT primary key (DEPTNO) using index; |
子表emp：
|
-- Create table create table EMP ( EMPNO NUMBER(4) not null, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ) -- Create/Recreate primary, unique and foreign key constraints alter table EMP add constraint PK_EMP primary key (EMPNO) using index ; alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO); |
一、主-外键约束FK
主-外键约束指的是子表的某一字段的内容取值范围必须由主表指定。
1）创建2张测试表：
create table f1 as select * from dept;
create table s1 as select * from emp;
说明：目前仅仅是复制了表结构及数据，未建立任何约束。
2）在未给f1.deptno创建主键或唯一约束，首选为s1.deptno创建外键：
SQL> alter table S1 add constraint FK_DEPTNO_S1 foreign key (DEPTNO) references F1 (DEPTNO);
alter table S1 add constraint FK_DEPTNO_S1 foreign key (DEPTNO) references F1 (DEPTNO)
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
结论：在子表中设置的与父表关联的外键字段时，这个字段在父表中必须具有主键约束或唯一约束才可以设置成功。否则无法设置外键约束。
3)首先为F1.DEPTNO创建主键约束，然后再在S1.DEPTNO设置依赖于F1的外键，即可创建成功。
SQL> alter table F1 add constraint PK_F1 primary key (DEPTNO);
Table altered.
SQL> alter table S1 add constraint FK_DEPTNO_S1 foreign key (DEPTNO) references F1 (DEPTNO);
Table altered.
4）删除F1的约束pk_f1：
SQL> alter table f1 drop constraint pk_f1;
alter table f1 drop constraint pk_f1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
结论：若想删除被外键依赖的主键信息时，需要首先删除与其关联的外键，然后再删除主键：
SQL> alter table s1 drop constraint fk_deptno_s1;
Table altered.
SQL> alter table f1 drop constraint pk_f1;
Table altered.
5）首先为F1.DEPTNO创建唯一约束，然后再在S1.DEPTNO设置依赖于F1的外键，即可创建成功。
SQL> alter table F1 add constraint UK_F1 unique(deptno);
Table altered.
SQL> alter table S1 add constraint FK_DEPTNO_S1 foreign key (DEPTNO) references F1 (DEPTNO);
Table altered.
6）与步骤4相同：
SQL> alter table f1 drop constraint uk_f1;
alter table f1 drop constraint uk_f1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
7）总结：外键依赖于父表字段的主键约束或唯一约束，有外键约束时，如果想删除主键或唯一约束需要先删除其外键约束。
8）针对于父表的delete或drop操作，如存在依赖关系需要首先处理子表数据，然后再处理父表数据。
9）针对于对父表的delete操作，外键设置on delete cascade：
create table f1 as select * from dept;
create table s1 as select * from emp;
alter table F1 add constraint PK_F1 primary key (DEPTNO)
alter table S1 add constraint FK_DEPTNO_S1 foreign key (DEPTNO) references F1 (DEPTNO) on delete cascade;
查询现在F1和S1表的数据：
SQL> select * from f1;
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from s1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20
7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30
7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10
14 rows selected.
删除F1表中10部门数据：
SQL> delete from f1 where deptno=10;
1 row deleted.
SQL> select * from f1;
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from s1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30
7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20
7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30
7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20
11 rows selected.
SQL> commit;
Commit complete.
结论：在外键中使用on delete cascade时，删除父表的数据时会同时删除与父表数据相关联的子表数据。
10）针对于对父表的delete操作，外键设置on delete set null：
create table f1 as select * from dept;
create table s1 as select * from emp;
alter table F1 add constraint PK_F1 primary key (DEPTNO);
alter table S1 add constraint FK_DEPTNO_S1 foreign key (DEPTNO) references F1 (DEPTNO) on delete set null;
查询F1和S1现在数据：
SQL> select * from f1;
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from s1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20
7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30
7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10
14 rows selected.
删除F1表中10部门的数据：
SQL> delete from f1 where deptno=10;
1 row deleted.
SQL> select * from f1;
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from s1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450
7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20
7839 KING PRESIDENT 1981/11/17 00:00:00 5000
7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30
7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300
14 rows selected.
SQL> commit;
Commit complete.
结论：在外键关联中使用on delete set null时，删除父表数据并不会同时删除与之关联的子表数据，而是将外键关联字段自动修改成null。
11）外键管理存在时，drop父表操作：
create table f1 as select * from dept;
create table s1 as select * from emp;
alter table F1 add constraint PK_F1 primary key (DEPTNO);
alter table S1 add constraint FK_DEPTNO_S1 foreign key (DEPTNO) references F1 (DEPTNO);
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('S1','F1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT
---------- -------------------- ----------
F1 PK_F1 P
S1 FK_DEPTNO_S1 R
删除F1表：
SQL> drop table F1;
drop table F1
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> drop table f1 cascade constraint;
Table dropped.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('S1','F1');
no rows selected
SQL> select * from tab where tname='S1';
TNAME TABTYPE CLUSTERID
------------------------------------------------------------------------------------------
S1 TABLE
结论：在drop父表时，如果使用cascade constraint时，会删除父表同时删除子表的外键约束信息，但是不会删除子表信息。
二、修改约束
1、为表add约束：
>语法：alter table table_name add constraint
创建测试表：
create table f1 as select * from dept;
create table s1 as select * from emp;
alter table F1 add constraint PK_F1 primary key (DEPTNO);
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('S1');
no rows selected
此时S1表没有任何字段。
1）为表S1.EMP添加主键约束：
SQL> alter table s1 add constraint pk_s1 primary key (empno);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT
---------- -------------------- ----------
S1 PK_S1 P
2）为表S1.SAL增加检查约束：在表中add约束时首先要保证已有数据没有违反约束。
SQL> alter table s1 add constraint ck_s1 check(sal > 100);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT
---------- -------------------- ----------
S1 CK_S1 C
S1 PK_S1 P
3）增加外键约束：
SQL> alter table s1 add constraint fk_s1 foreign key(deptno) references f1(deptno);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT
---------- -------------------- ----------
S1 CK_S1 C
S1 FK_S1 R
S1 PK_S1 P
4）增加唯一约束：
SQL> alter table s1 add constraint uk_s1 unique(ename);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT
---------- -------------------- ----------
S1 CK_S1 C
S1 FK_S1 R
S1 UK_S1 U
S1 PK_S1 P
5）增加非空约束：不能通过alter table table_name add constraint命令添加，只能通过修改表的方式增加：
SQL> alter table s1 modify(SAL NUMBER(7,2) not null);
Table altered.
create table S1
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2) not null,
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
2、启用/禁用约束
>禁用约束语法：alter table table_name disable constraint cons_name [cascade];
>启用约束语法：alter table table_name enable constraint cons_name;
1）禁用表S1的约束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
S1 CK_S1 C ENABLED
S1 FK_S1 R ENABLED
S1 UK_S1 U ENABLED
S1 PK_S1 P ENABLED
SQL> ALTER TABLE S1 DISABLE CONSTRAINT CK_S1;
Table altered.
SQL> ALTER TABLE S1 DISABLE CONSTRAINT UK_S1;
Table altered.
SQL> ALTER TABLE S1 DISABLE CONSTRAINT PK_S1;
Table altered.
SQL> ALTER TABLE S1 DISABLE CONSTRAINT FK_S1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
S1 CK_S1 C DISABLED
S1 FK_S1 R DISABLED
S1 UK_S1 U DISABLED
S1 PK_S1 P DISABLED
2）启用表S1约束
SQL> ALTER TABLE S1 ENABLE CONSTRAINT PK_S1;
Table altered.
SQL> ALTER TABLE S1 ENABLE CONSTRAINT CK_S1;
Table altered.
SQL> ALTER TABLE S1 ENABLE CONSTRAINT UK_S1;
Table altered.
SQL> ALTER TABLE S1 ENABLE CONSTRAINT FK_S1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
S1 CK_S1 C ENABLED
S1 FK_S1 R ENABLED
S1 UK_S1 U ENABLED
S1 PK_S1 P ENABLED
3）禁用F1表上的pk_f1约束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
S1 CK_S1 C ENABLED
S1 FK_S1 R ENABLED
S1 UK_S1 U ENABLED
S1 PK_S1 P ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('F1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
F1 PK_F1 P ENABLED
SQL> ALTER TABLE F1 DISABLE CONSTRAINT PK_F1;
ALTER TABLE F1 DISABLE CONSTRAINT PK_F1
*
ERROR at line 1:
ORA-02297: cannot disable constraint (SCOTT.PK_F1) - dependencies exist
注意：此处因为PK_F1存在外键约束，所以禁止禁用PK_F1约束。
SQL> ALTER TABLE F1 DISABLE CONSTRAINT PK_F1 CASCADE;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('F1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
F1 PK_F1 P DISABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
S1 CK_S1 C ENABLED
S1 FK_S1 R DISABLED
S1 UK_S1 U ENABLED
S1 PK_S1 P ENABLED
注意：禁用父表带有外键的约束时，可以采用cascade方式禁用约束，该方式同时也会禁用掉子表的外键约束。
3、删除约束：
语法：alter table table_name drop constraint cons_name [cascade];
1）删除S1中非外键约束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
S1 CK_S1 C ENABLED
S1 FK_S1 R ENABLED
S1 UK_S1 U ENABLED
S1 PK_S1 P ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('F1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
F1 PK_F1 P ENABLED
SQL> ALTER TABLE S1 DROP CONSTRAINT CK_S1;
Table altered.
SQL> ALTER TABLE S1 DROP CONSTRAINT UK_S1;
Table altered.
SQL> ALTER TABLE S1 DROP CONSTRAINT PK_S1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
S1 FK_S1 R ENABLED
S1 SYS_C0019909 C ENABLED
2）删除F1表中的pk_f1约束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
S1 FK_S1 R ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('F1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
F1 PK_F1 P ENABLED
SQL> ALTER TABLE F1 DROP CONSTRAINT PK_F1;
ALTER TABLE F1 DROP CONSTRAINT PK_F1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
注意：不允许删除带有外键约束的主表约束。
SQL> ALTER TABLE F1 DROP CONSTRAINT PK_F1 CASCADE;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('F1');
no rows selected
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('S1');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
no rows selected
注意：cascade删除父表约束时，会同时删除子表约束。
三、查看约束信息：
通过user_constraint和user_cons_columns字典表查询约束信息，同时也以次字典表为基础编写约束日常监控脚本：
>CONSTRAINT_TYPE类型：P、R、C、C（not null）、Q
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('EMP');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
EMP PK_EMP P ENABLED
EMP FK_DEPTNO R ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('DEPT');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT STATUS
---------- -------------------- ---------- ------------------------
DEPT PK_DEPT P ENABLED