oracle 主外來鍵關係及實驗
參照scott下的dept(主表），emp(子表），定義如下：
表dept定義
SQL> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
--表emp定義
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>
一、主-外來鍵約束FK
主-外來鍵約束指的是子表的某一欄位的內容取值範圍必須由主表指定。
1）建立2張測試表：
create table d1 as select * from dept;
create table e1 as select * from emp;
說明：目前僅僅是複製了表結構及資料，未建立任何約束。
2）在未給d1.deptno建立主鍵或唯一約束，首選為e1.deptno建立外來鍵：
SQL> alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO);
alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO)
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
結論：在子表中設定的與父表關聯的外來鍵欄位時，這個欄位在父表中必須具有主鍵約束或唯一約束才可以設定成功。否則無法設定外來鍵約束。
3)首先為d1.DEPTNO建立主鍵約束，然後再在e1.DEPTNO設定依賴於d1的外來鍵，即可建立成功。
SQL> alter table d1 add constraint PK_d1 primary key (DEPTNO);
Table altered.
SQL> alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO);
Table altered.
4）刪除d1的約束pk_d1：
--查詢：
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
--操作：
SQL> alter table d1 drop constraint pk_d1;
alter table d1 drop constraint pk_d1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
結論：若想刪除被外來鍵依賴的主鍵資訊時，需要首先刪除與其關聯的外來鍵，然後再刪除主鍵：
SQL> alter table e1 drop constraint fk_deptno_e1;
Table altered.
SQL> alter table d1 drop constraint pk_d1;
Table altered.
--再次查詢：
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
5）再次設定主外來鍵：首先為d1.DEPTNO建立唯一約束，然後再在e1.DEPTNO設定依賴於d1的外來鍵，即可建立成功。
SQL> alter table d1 add constraint UK_d1 unique(deptno);
Table altered.
SQL> alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO);
Table altered.
6）與步驟4相同：
SQL> alter table d1 drop constraint uk_d1;
alter table d1 drop constraint uk_d1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
--刪除外來鍵
SQL> alter table e1 drop constraint fk_deptno_e1;
Table altered.
--刪除主鍵
SQL> alter table d1 drop constraint uk_d1;
Table altered.
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
7）總結：外來鍵依賴於父表欄位的主鍵約束或唯一約束，有外來鍵約束時，如果想刪除主鍵或唯一約束需要先刪除其外來鍵約束。
8）針對於父表的delete或drop操作，如存在依賴關係需要首先處理子表資料，然後再處理父表資料。
9）針對於對父表的delete操作，外來鍵設定on delete cascade：
drop table d1 purge;
drop table e1 purge;
create table d1 as select * from dept;
create table e1 as select * from emp;
alter table d1 add constraint PK_d1 primary key (DEPTNO);
alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO) on delete cascade;
--查詢現在d1和e1表的資料：
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
--刪除d1表中10部門資料：
SQL> delete from d1 where deptno=10;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
10 rows selected.
結論：在外來鍵中使用on delete cascade時，刪除父表的資料時會同時刪除與父表資料相關聯的子表資料。
10）針對於對父表的delete操作，外來鍵設定on delete set null：
drop table e1 purge;
drop table d1 purge;
create table d1 as select * from dept;
create table e1 as select * from emp;
alter table d1 add constraint PK_d1 primary key (DEPTNO);
alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO) on delete set null;
--查詢d1和e1現在資料：
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
--刪除d1表中10部門的資料：
SQL> delete from d1 where deptno=10;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7839 KING PRESIDENT 17-NOV-81 5000
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300
7782 CLARK MANAGER 7839 09-JUN-81 2450
13 rows selected.
發現dept=10的值變為null了
結論：在外來鍵關聯中使用on delete set null時，刪除父表資料並不會同時刪除與之關聯的子表資料，而是將外來鍵關聯欄位自動修改成null。
11）外來鍵管理存在時，drop父表操作：
drop table e1 purge;
drop table d1 purge;
create table d1 as select * from dept;
create table e1 as select * from emp;
alter table d1 add constraint PK_d1 primary key (DEPTNO);
alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO);
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1','D1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
D1 PK_D1 P
E1 FK_DEPTNO_E1 R
--刪除d1表：
SQL> drop table d1;
drop table d1
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
--使用drop table table_name cascade constraint子句刪除：
SQL> drop table d1 cascade constraint;
Table dropped.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1','D1');
no rows selected
--查詢
SQL> select * from d1;
select * from d1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
結論：在drop父表時，如果使用cascade constraint時，會刪除父表同時刪除子表的外來鍵約束資訊，但是不會刪除子表資訊。
二、修改約束
1、為表add約束：
>語法：alter table table_name add constraint
建立測試表：
drop table e1 purge;
drop table d1 purge;
create table d1 as select * from dept;
create table e1 as select * from emp;
alter table d1 add constraint PK_d1 primary key (DEPTNO);
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
no rows selected
此時e1表沒有任何欄位。
1）為表e1.EMP新增主鍵約束：
SQL> alter table e1 add constraint pk_e1 primary key (empno);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
E1 PK_E1 P
2）為表e1.SAL增加檢查約束：在表中add約束時首先要保證已有資料沒有違反約束。
SQL> alter table e1 add constraint ck_e1 check(sal > 100);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
E1 PK_E1 P
E1 CK_E1 C
3）增加外來鍵約束：
SQL> alter table e1 add constraint fk_e1 foreign key(deptno) references d1(deptno);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
E1 PK_E1 P
E1 CK_E1 C
E1 FK_E1 R
4）增加唯一約束：
SQL> alter table e1 add constraint uk_e1 unique(ename);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
E1 PK_E1 P
E1 CK_E1 C
E1 FK_E1 R
E1 UK_E1 U
5）增加非空約束：不能透過alter table table_name add constraint命令新增，只能透過修改表的方式增加：
SQL> alter table e1 modify(SAL NUMBER(7,2) not null);
Table altered.
SQL> set lines 200
SQL> col owner for a10
SQL> col TABLE_NAME for a15
SQL> col SEARCH_CONDITION for a25
SQL> col column_name for a15
SQL> col index_owner for a10
SQL> col index_name for a15
SQL> col CONSTRAINT_NAME for a15
SQL> select a.OWNER,
2 a.TABLE_NAME,
3 a.CONSTRAINT_NAME,
4 a.CONSTRAINT_TYPE,
5 a.SEARCH_CONDITION,
6 a.STATUS,
7 a.INVALID,
8 a.INDEX_OWNER,
9 a.INDEX_NAME,
10 b.CONSTRAINT_NAME,
11 b.COLUMN_NAME
12 from dba_constraints a, dba_cons_columns b
13 where a.owner = b.owner and a.owner='SCOTT'
14 and a.TABLE_NAME = b.TABLE_NAME and A.TABLE_NAME='E1';
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 PK_E1 EMPNO
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 CK_E1 SAL
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 FK_E1 DEPTNO
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 UK_E1 ENAME
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 SYS_C0012368 SAL
SCOTT E1 CK_E1 C sal > 100 ENABLED PK_E1 EMPNO
SCOTT E1 CK_E1 C sal > 100 ENABLED CK_E1 SAL
SCOTT E1 CK_E1 C sal > 100 ENABLED FK_E1 DEPTNO
SCOTT E1 CK_E1 C sal > 100 ENABLED UK_E1 ENAME
SCOTT E1 CK_E1 C sal > 100 ENABLED SYS_C0012368 SAL
SCOTT E1 FK_E1 R ENABLED PK_E1 EMPNO
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 FK_E1 R ENABLED CK_E1 SAL
SCOTT E1 FK_E1 R ENABLED FK_E1 DEPTNO
SCOTT E1 FK_E1 R ENABLED UK_E1 ENAME
SCOTT E1 FK_E1 R ENABLED SYS_C0012368 SAL
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 PK_E1 EMPNO
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 CK_E1 SAL
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 FK_E1 DEPTNO
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 UK_E1 ENAME
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 SYS_C0012368 SAL
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED PK_E1 EMPNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED CK_E1 SAL
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED FK_E1 DEPTNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED UK_E1 ENAME
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED SYS_C0012368 SAL
25 rows selected.
2、啟用/禁用約束
>禁用約束語法：alter table table_name disable constraint cons_name [cascade];
>啟用約束語法：alter table table_name enable constraint cons_name;
1）禁用表e1的約束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R ENABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
--禁用約束
SQL> ALTER TABLE e1 DISABLE CONSTRAINT CK_e1;
Table altered.
SQL> ALTER TABLE e1 DISABLE CONSTRAINT UK_e1;
Table altered.
SQL> ALTER TABLE e1 DISABLE CONSTRAINT PK_e1;
Table altered.
SQL> ALTER TABLE e1 DISABLE CONSTRAINT FK_e1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P DISABLED
E1 CK_E1 C DISABLED
E1 FK_E1 R DISABLED
E1 UK_E1 U DISABLED
E1 SYS_C0012368 C ENABLED
--再次查詢
SQL> select a.OWNER,
2 a.TABLE_NAME,
3 a.CONSTRAINT_NAME,
4 a.CONSTRAINT_TYPE,
5 a.SEARCH_CONDITION,
6 a.STATUS,
7 a.INVALID,
8 a.INDEX_OWNER,
9 a.INDEX_NAME,
10 b.CONSTRAINT_NAME,
11 b.COLUMN_NAME
12 from dba_constraints a, dba_cons_columns b
13 where a.owner = b.owner and a.owner='SCOTT'
14 and a.TABLE_NAME = b.TABLE_NAME and A.TABLE_NAME='E1';
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 PK_E1 P DISABLED PK_E1 EMPNO
SCOTT E1 PK_E1 P DISABLED CK_E1 SAL
SCOTT E1 PK_E1 P DISABLED FK_E1 DEPTNO
SCOTT E1 PK_E1 P DISABLED UK_E1 ENAME
SCOTT E1 PK_E1 P DISABLED SYS_C0012368 SAL
SCOTT E1 CK_E1 C sal > 100 DISABLED PK_E1 EMPNO
SCOTT E1 CK_E1 C sal > 100 DISABLED CK_E1 SAL
SCOTT E1 CK_E1 C sal > 100 DISABLED FK_E1 DEPTNO
SCOTT E1 CK_E1 C sal > 100 DISABLED UK_E1 ENAME
SCOTT E1 CK_E1 C sal > 100 DISABLED SYS_C0012368 SAL
SCOTT E1 FK_E1 R DISABLED PK_E1 EMPNO
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 FK_E1 R DISABLED CK_E1 SAL
SCOTT E1 FK_E1 R DISABLED FK_E1 DEPTNO
SCOTT E1 FK_E1 R DISABLED UK_E1 ENAME
SCOTT E1 FK_E1 R DISABLED SYS_C0012368 SAL
SCOTT E1 UK_E1 U DISABLED PK_E1 EMPNO
SCOTT E1 UK_E1 U DISABLED CK_E1 SAL
SCOTT E1 UK_E1 U DISABLED FK_E1 DEPTNO
SCOTT E1 UK_E1 U DISABLED UK_E1 ENAME
SCOTT E1 UK_E1 U DISABLED SYS_C0012368 SAL
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED PK_E1 EMPNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED CK_E1 SAL
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED FK_E1 DEPTNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED UK_E1 ENAME
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED SYS_C0012368 SAL
25 rows selected.
2）啟用表e1約束
SQL> ALTER TABLE e1 ENABLE CONSTRAINT PK_e1;
Table altered.
SQL> ALTER TABLE e1 ENABLE CONSTRAINT CK_e1;
Table altered.
SQL> ALTER TABLE e1 ENABLE CONSTRAINT UK_e1;
Table altered.
SQL> ALTER TABLE e1 ENABLE CONSTRAINT FK_e1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R ENABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select a.OWNER,
a.TABLE_NAME,
2 3 a.CONSTRAINT_NAME,
4 a.CONSTRAINT_TYPE,
5 a.SEARCH_CONDITION,
6 a.STATUS,
7 a.INVALID,
8 a.INDEX_OWNER,
9 a.INDEX_NAME,
10 b.CONSTRAINT_NAME,
11 b.COLUMN_NAME
12 from dba_constraints a, dba_cons_columns b
13 where a.owner = b.owner and a.owner='SCOTT'
14 and a.TABLE_NAME = b.TABLE_NAME and A.TABLE_NAME='E1';
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 PK_E1 EMPNO
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 CK_E1 SAL
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 FK_E1 DEPTNO
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 UK_E1 ENAME
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 SYS_C0012368 SAL
SCOTT E1 CK_E1 C sal > 100 ENABLED PK_E1 EMPNO
SCOTT E1 CK_E1 C sal > 100 ENABLED CK_E1 SAL
SCOTT E1 CK_E1 C sal > 100 ENABLED FK_E1 DEPTNO
SCOTT E1 CK_E1 C sal > 100 ENABLED UK_E1 ENAME
SCOTT E1 CK_E1 C sal > 100 ENABLED SYS_C0012368 SAL
SCOTT E1 FK_E1 R ENABLED PK_E1 EMPNO
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 FK_E1 R ENABLED CK_E1 SAL
SCOTT E1 FK_E1 R ENABLED FK_E1 DEPTNO
SCOTT E1 FK_E1 R ENABLED UK_E1 ENAME
SCOTT E1 FK_E1 R ENABLED SYS_C0012368 SAL
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 PK_E1 EMPNO
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 CK_E1 SAL
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 FK_E1 DEPTNO
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 UK_E1 ENAME
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 SYS_C0012368 SAL
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED PK_E1 EMPNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED CK_E1 SAL
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED FK_E1 DEPTNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED UK_E1 ENAME
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED SYS_C0012368 SAL
25 rows selected.
3）禁用d1表上的pk_d1約束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R ENABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P ENABLED
SQL> ALTER TABLE d1 DISABLE CONSTRAINT PK_d1;
ALTER TABLE d1 DISABLE CONSTRAINT PK_d1
*
ERROR at line 1:
ORA-02297: cannot disable constraint (SCOTT.PK_D1) - dependencies exist
注意：此處因為PK_d1存在外來鍵約束，所以禁止禁用PK_d1約束。
--使用語法ALTER TABLE table_name DISABLE CONSTRAINT cons_name CASCADE;
SQL> ALTER TABLE d1 DISABLE CONSTRAINT PK_d1 CASCADE;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R DISABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P DISABLED
注意：禁用父錶帶有外來鍵的約束時，可以採用cascade方式禁用約束，該方式同時也會禁用掉子表的外來鍵約束。
3、刪除約束：
語法：alter table table_name drop constraint cons_name [cascade];
1）刪除e1中非外來鍵約束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R DISABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P DISABLED
SQL> ALTER TABLE e1 DROP CONSTRAINT CK_e1;
Table altered.
SQL> ALTER TABLE e1 DROP CONSTRAINT UK_e1;
Table altered.
SQL> ALTER TABLE e1 DROP CONSTRAINT PK_e1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 FK_E1 R DISABLED
E1 SYS_C0012368 C ENABLED
2）刪除d1表中的pk_d1約束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 FK_E1 R DISABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P DISABLED
SQL> alter table D1 enable constraint PK_D1;
Table altered.
SQL> alter table e1 enable constraint FK_E1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 FK_E1 R ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P ENABLED
SQL> ALTER TABLE d1 DROP CONSTRAINT PK_d1;
ALTER TABLE d1 DROP CONSTRAINT PK_d1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
注意：不允許刪除帶有外來鍵約束的主表約束。
--使用ALTER TABLE tab_name DROP CONSTRAINT cons_name CASCADE刪除，如下：
SQL> ALTER TABLE d1 DROP CONSTRAINT PK_d1 CASCADE;
Table altered.
--查詢：
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
no rows selected
注意：cascade刪除父表約束時，會同時刪除子表約束。
三、檢視約束資訊：
透過dba_constraint和dba_cons_columns字典表查詢約束資訊，同時也以此字典表為基礎編寫約束日常監控指令碼：
>CONSTRAINT_TYPE型別：P、R、C、C（not null）
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('EMP');
TABLE_NAME CONSTRAINT_NAME C 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 C STATUS
--------------- --------------- - --------
DEPT PK_DEPT P ENABLED
表dept定義
SQL> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
--表emp定義
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>
一、主-外來鍵約束FK
主-外來鍵約束指的是子表的某一欄位的內容取值範圍必須由主表指定。
1）建立2張測試表：
create table d1 as select * from dept;
create table e1 as select * from emp;
說明：目前僅僅是複製了表結構及資料，未建立任何約束。
2）在未給d1.deptno建立主鍵或唯一約束，首選為e1.deptno建立外來鍵：
SQL> alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO);
alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO)
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
結論：在子表中設定的與父表關聯的外來鍵欄位時，這個欄位在父表中必須具有主鍵約束或唯一約束才可以設定成功。否則無法設定外來鍵約束。
3)首先為d1.DEPTNO建立主鍵約束，然後再在e1.DEPTNO設定依賴於d1的外來鍵，即可建立成功。
SQL> alter table d1 add constraint PK_d1 primary key (DEPTNO);
Table altered.
SQL> alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO);
Table altered.
4）刪除d1的約束pk_d1：
--查詢：
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
--操作：
SQL> alter table d1 drop constraint pk_d1;
alter table d1 drop constraint pk_d1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
結論：若想刪除被外來鍵依賴的主鍵資訊時，需要首先刪除與其關聯的外來鍵，然後再刪除主鍵：
SQL> alter table e1 drop constraint fk_deptno_e1;
Table altered.
SQL> alter table d1 drop constraint pk_d1;
Table altered.
--再次查詢：
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
5）再次設定主外來鍵：首先為d1.DEPTNO建立唯一約束，然後再在e1.DEPTNO設定依賴於d1的外來鍵，即可建立成功。
SQL> alter table d1 add constraint UK_d1 unique(deptno);
Table altered.
SQL> alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO);
Table altered.
6）與步驟4相同：
SQL> alter table d1 drop constraint uk_d1;
alter table d1 drop constraint uk_d1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
--刪除外來鍵
SQL> alter table e1 drop constraint fk_deptno_e1;
Table altered.
--刪除主鍵
SQL> alter table d1 drop constraint uk_d1;
Table altered.
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
7）總結：外來鍵依賴於父表欄位的主鍵約束或唯一約束，有外來鍵約束時，如果想刪除主鍵或唯一約束需要先刪除其外來鍵約束。
8）針對於父表的delete或drop操作，如存在依賴關係需要首先處理子表資料，然後再處理父表資料。
9）針對於對父表的delete操作，外來鍵設定on delete cascade：
drop table d1 purge;
drop table e1 purge;
create table d1 as select * from dept;
create table e1 as select * from emp;
alter table d1 add constraint PK_d1 primary key (DEPTNO);
alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO) on delete cascade;
--查詢現在d1和e1表的資料：
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
--刪除d1表中10部門資料：
SQL> delete from d1 where deptno=10;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
10 rows selected.
結論：在外來鍵中使用on delete cascade時，刪除父表的資料時會同時刪除與父表資料相關聯的子表資料。
10）針對於對父表的delete操作，外來鍵設定on delete set null：
drop table e1 purge;
drop table d1 purge;
create table d1 as select * from dept;
create table e1 as select * from emp;
alter table d1 add constraint PK_d1 primary key (DEPTNO);
alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO) on delete set null;
--查詢d1和e1現在資料：
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
--刪除d1表中10部門的資料：
SQL> delete from d1 where deptno=10;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7839 KING PRESIDENT 17-NOV-81 5000
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300
7782 CLARK MANAGER 7839 09-JUN-81 2450
13 rows selected.
發現dept=10的值變為null了
結論：在外來鍵關聯中使用on delete set null時，刪除父表資料並不會同時刪除與之關聯的子表資料，而是將外來鍵關聯欄位自動修改成null。
11）外來鍵管理存在時，drop父表操作：
drop table e1 purge;
drop table d1 purge;
create table d1 as select * from dept;
create table e1 as select * from emp;
alter table d1 add constraint PK_d1 primary key (DEPTNO);
alter table e1 add constraint FK_DEPTNO_e1 foreign key (DEPTNO) references d1 (DEPTNO);
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1','D1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
D1 PK_D1 P
E1 FK_DEPTNO_E1 R
--刪除d1表：
SQL> drop table d1;
drop table d1
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
--使用drop table table_name cascade constraint子句刪除：
SQL> drop table d1 cascade constraint;
Table dropped.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1','D1');
no rows selected
--查詢
SQL> select * from d1;
select * from d1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from e1 order by deptno asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
13 rows selected.
結論：在drop父表時，如果使用cascade constraint時，會刪除父表同時刪除子表的外來鍵約束資訊，但是不會刪除子表資訊。
二、修改約束
1、為表add約束：
>語法：alter table table_name add constraint
建立測試表：
drop table e1 purge;
drop table d1 purge;
create table d1 as select * from dept;
create table e1 as select * from emp;
alter table d1 add constraint PK_d1 primary key (DEPTNO);
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
no rows selected
此時e1表沒有任何欄位。
1）為表e1.EMP新增主鍵約束：
SQL> alter table e1 add constraint pk_e1 primary key (empno);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
E1 PK_E1 P
2）為表e1.SAL增加檢查約束：在表中add約束時首先要保證已有資料沒有違反約束。
SQL> alter table e1 add constraint ck_e1 check(sal > 100);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
E1 PK_E1 P
E1 CK_E1 C
3）增加外來鍵約束：
SQL> alter table e1 add constraint fk_e1 foreign key(deptno) references d1(deptno);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
E1 PK_E1 P
E1 CK_E1 C
E1 FK_E1 R
4）增加唯一約束：
SQL> alter table e1 add constraint uk_e1 unique(ename);
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
E1 PK_E1 P
E1 CK_E1 C
E1 FK_E1 R
E1 UK_E1 U
5）增加非空約束：不能透過alter table table_name add constraint命令新增，只能透過修改表的方式增加：
SQL> alter table e1 modify(SAL NUMBER(7,2) not null);
Table altered.
SQL> set lines 200
SQL> col owner for a10
SQL> col TABLE_NAME for a15
SQL> col SEARCH_CONDITION for a25
SQL> col column_name for a15
SQL> col index_owner for a10
SQL> col index_name for a15
SQL> col CONSTRAINT_NAME for a15
SQL> select a.OWNER,
2 a.TABLE_NAME,
3 a.CONSTRAINT_NAME,
4 a.CONSTRAINT_TYPE,
5 a.SEARCH_CONDITION,
6 a.STATUS,
7 a.INVALID,
8 a.INDEX_OWNER,
9 a.INDEX_NAME,
10 b.CONSTRAINT_NAME,
11 b.COLUMN_NAME
12 from dba_constraints a, dba_cons_columns b
13 where a.owner = b.owner and a.owner='SCOTT'
14 and a.TABLE_NAME = b.TABLE_NAME and A.TABLE_NAME='E1';
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 PK_E1 EMPNO
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 CK_E1 SAL
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 FK_E1 DEPTNO
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 UK_E1 ENAME
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 SYS_C0012368 SAL
SCOTT E1 CK_E1 C sal > 100 ENABLED PK_E1 EMPNO
SCOTT E1 CK_E1 C sal > 100 ENABLED CK_E1 SAL
SCOTT E1 CK_E1 C sal > 100 ENABLED FK_E1 DEPTNO
SCOTT E1 CK_E1 C sal > 100 ENABLED UK_E1 ENAME
SCOTT E1 CK_E1 C sal > 100 ENABLED SYS_C0012368 SAL
SCOTT E1 FK_E1 R ENABLED PK_E1 EMPNO
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 FK_E1 R ENABLED CK_E1 SAL
SCOTT E1 FK_E1 R ENABLED FK_E1 DEPTNO
SCOTT E1 FK_E1 R ENABLED UK_E1 ENAME
SCOTT E1 FK_E1 R ENABLED SYS_C0012368 SAL
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 PK_E1 EMPNO
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 CK_E1 SAL
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 FK_E1 DEPTNO
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 UK_E1 ENAME
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 SYS_C0012368 SAL
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED PK_E1 EMPNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED CK_E1 SAL
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED FK_E1 DEPTNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED UK_E1 ENAME
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED SYS_C0012368 SAL
25 rows selected.
2、啟用/禁用約束
>禁用約束語法：alter table table_name disable constraint cons_name [cascade];
>啟用約束語法：alter table table_name enable constraint cons_name;
1）禁用表e1的約束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R ENABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
--禁用約束
SQL> ALTER TABLE e1 DISABLE CONSTRAINT CK_e1;
Table altered.
SQL> ALTER TABLE e1 DISABLE CONSTRAINT UK_e1;
Table altered.
SQL> ALTER TABLE e1 DISABLE CONSTRAINT PK_e1;
Table altered.
SQL> ALTER TABLE e1 DISABLE CONSTRAINT FK_e1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P DISABLED
E1 CK_E1 C DISABLED
E1 FK_E1 R DISABLED
E1 UK_E1 U DISABLED
E1 SYS_C0012368 C ENABLED
--再次查詢
SQL> select a.OWNER,
2 a.TABLE_NAME,
3 a.CONSTRAINT_NAME,
4 a.CONSTRAINT_TYPE,
5 a.SEARCH_CONDITION,
6 a.STATUS,
7 a.INVALID,
8 a.INDEX_OWNER,
9 a.INDEX_NAME,
10 b.CONSTRAINT_NAME,
11 b.COLUMN_NAME
12 from dba_constraints a, dba_cons_columns b
13 where a.owner = b.owner and a.owner='SCOTT'
14 and a.TABLE_NAME = b.TABLE_NAME and A.TABLE_NAME='E1';
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 PK_E1 P DISABLED PK_E1 EMPNO
SCOTT E1 PK_E1 P DISABLED CK_E1 SAL
SCOTT E1 PK_E1 P DISABLED FK_E1 DEPTNO
SCOTT E1 PK_E1 P DISABLED UK_E1 ENAME
SCOTT E1 PK_E1 P DISABLED SYS_C0012368 SAL
SCOTT E1 CK_E1 C sal > 100 DISABLED PK_E1 EMPNO
SCOTT E1 CK_E1 C sal > 100 DISABLED CK_E1 SAL
SCOTT E1 CK_E1 C sal > 100 DISABLED FK_E1 DEPTNO
SCOTT E1 CK_E1 C sal > 100 DISABLED UK_E1 ENAME
SCOTT E1 CK_E1 C sal > 100 DISABLED SYS_C0012368 SAL
SCOTT E1 FK_E1 R DISABLED PK_E1 EMPNO
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 FK_E1 R DISABLED CK_E1 SAL
SCOTT E1 FK_E1 R DISABLED FK_E1 DEPTNO
SCOTT E1 FK_E1 R DISABLED UK_E1 ENAME
SCOTT E1 FK_E1 R DISABLED SYS_C0012368 SAL
SCOTT E1 UK_E1 U DISABLED PK_E1 EMPNO
SCOTT E1 UK_E1 U DISABLED CK_E1 SAL
SCOTT E1 UK_E1 U DISABLED FK_E1 DEPTNO
SCOTT E1 UK_E1 U DISABLED UK_E1 ENAME
SCOTT E1 UK_E1 U DISABLED SYS_C0012368 SAL
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED PK_E1 EMPNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED CK_E1 SAL
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED FK_E1 DEPTNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED UK_E1 ENAME
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED SYS_C0012368 SAL
25 rows selected.
2）啟用表e1約束
SQL> ALTER TABLE e1 ENABLE CONSTRAINT PK_e1;
Table altered.
SQL> ALTER TABLE e1 ENABLE CONSTRAINT CK_e1;
Table altered.
SQL> ALTER TABLE e1 ENABLE CONSTRAINT UK_e1;
Table altered.
SQL> ALTER TABLE e1 ENABLE CONSTRAINT FK_e1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R ENABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select a.OWNER,
a.TABLE_NAME,
2 3 a.CONSTRAINT_NAME,
4 a.CONSTRAINT_TYPE,
5 a.SEARCH_CONDITION,
6 a.STATUS,
7 a.INVALID,
8 a.INDEX_OWNER,
9 a.INDEX_NAME,
10 b.CONSTRAINT_NAME,
11 b.COLUMN_NAME
12 from dba_constraints a, dba_cons_columns b
13 where a.owner = b.owner and a.owner='SCOTT'
14 and a.TABLE_NAME = b.TABLE_NAME and A.TABLE_NAME='E1';
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 PK_E1 EMPNO
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 CK_E1 SAL
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 FK_E1 DEPTNO
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 UK_E1 ENAME
SCOTT E1 PK_E1 P ENABLED SCOTT PK_E1 SYS_C0012368 SAL
SCOTT E1 CK_E1 C sal > 100 ENABLED PK_E1 EMPNO
SCOTT E1 CK_E1 C sal > 100 ENABLED CK_E1 SAL
SCOTT E1 CK_E1 C sal > 100 ENABLED FK_E1 DEPTNO
SCOTT E1 CK_E1 C sal > 100 ENABLED UK_E1 ENAME
SCOTT E1 CK_E1 C sal > 100 ENABLED SYS_C0012368 SAL
SCOTT E1 FK_E1 R ENABLED PK_E1 EMPNO
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 FK_E1 R ENABLED CK_E1 SAL
SCOTT E1 FK_E1 R ENABLED FK_E1 DEPTNO
SCOTT E1 FK_E1 R ENABLED UK_E1 ENAME
SCOTT E1 FK_E1 R ENABLED SYS_C0012368 SAL
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 PK_E1 EMPNO
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 CK_E1 SAL
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 FK_E1 DEPTNO
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 UK_E1 ENAME
SCOTT E1 UK_E1 U ENABLED SCOTT UK_E1 SYS_C0012368 SAL
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED PK_E1 EMPNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED CK_E1 SAL
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS INVALID INDEX_OWNE INDEX_NAME CONSTRAINT_NAME COLUMN_NAME
---------- --------------- --------------- - ------------------------- -------- ------- ---------- --------------- --------------- ---------------
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED FK_E1 DEPTNO
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED UK_E1 ENAME
SCOTT E1 SYS_C0012368 C "SAL" IS NOT NULL ENABLED SYS_C0012368 SAL
25 rows selected.
3）禁用d1表上的pk_d1約束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R ENABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P ENABLED
SQL> ALTER TABLE d1 DISABLE CONSTRAINT PK_d1;
ALTER TABLE d1 DISABLE CONSTRAINT PK_d1
*
ERROR at line 1:
ORA-02297: cannot disable constraint (SCOTT.PK_D1) - dependencies exist
注意：此處因為PK_d1存在外來鍵約束，所以禁止禁用PK_d1約束。
--使用語法ALTER TABLE table_name DISABLE CONSTRAINT cons_name CASCADE;
SQL> ALTER TABLE d1 DISABLE CONSTRAINT PK_d1 CASCADE;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R DISABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P DISABLED
注意：禁用父錶帶有外來鍵的約束時，可以採用cascade方式禁用約束，該方式同時也會禁用掉子表的外來鍵約束。
3、刪除約束：
語法：alter table table_name drop constraint cons_name [cascade];
1）刪除e1中非外來鍵約束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 PK_E1 P ENABLED
E1 CK_E1 C ENABLED
E1 FK_E1 R DISABLED
E1 UK_E1 U ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P DISABLED
SQL> ALTER TABLE e1 DROP CONSTRAINT CK_e1;
Table altered.
SQL> ALTER TABLE e1 DROP CONSTRAINT UK_e1;
Table altered.
SQL> ALTER TABLE e1 DROP CONSTRAINT PK_e1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 FK_E1 R DISABLED
E1 SYS_C0012368 C ENABLED
2）刪除d1表中的pk_d1約束
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 FK_E1 R DISABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P DISABLED
SQL> alter table D1 enable constraint PK_D1;
Table altered.
SQL> alter table e1 enable constraint FK_E1;
Table altered.
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 FK_E1 R ENABLED
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
D1 PK_D1 P ENABLED
SQL> ALTER TABLE d1 DROP CONSTRAINT PK_d1;
ALTER TABLE d1 DROP CONSTRAINT PK_d1
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
注意：不允許刪除帶有外來鍵約束的主表約束。
--使用ALTER TABLE tab_name DROP CONSTRAINT cons_name CASCADE刪除，如下：
SQL> ALTER TABLE d1 DROP CONSTRAINT PK_d1 CASCADE;
Table altered.
--查詢：
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('E1');
TABLE_NAME CONSTRAINT_NAME C STATUS
--------------- --------------- - --------
E1 SYS_C0012368 C ENABLED
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('D1');
no rows selected
注意：cascade刪除父表約束時，會同時刪除子表約束。
三、檢視約束資訊：
透過dba_constraint和dba_cons_columns字典表查詢約束資訊，同時也以此字典表為基礎編寫約束日常監控指令碼：
>CONSTRAINT_TYPE型別：P、R、C、C（not null）
SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS from user_constraints where table_name in ('EMP');
TABLE_NAME CONSTRAINT_NAME C 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 C STATUS
--------------- --------------- - --------
DEPT PK_DEPT P ENABLED
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2155606/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 求主外來鍵的關係
- 檢視主外來鍵關係
- mysql主外來鍵依賴關係MySql
- ORACLE查詢表之間的主外來鍵關係Oracle
- 資料庫的主外來鍵關係資料庫
- EntityFramework Core 遷移忽略主外來鍵關係Framework
- Oracle 查詢表與表之間的 主外來鍵關係Oracle
- 求主外來鍵的關係的指令碼(ZT)指令碼
- MySQL建立資料表並建立主外來鍵關係MySql
- 關於主外來鍵關係DML父表和DML子表加鎖方式
- MySQL 檢視資料庫中有主外來鍵關係的表資訊MySql資料庫
- oracle主外來鍵鎖＿lock_約束Oracle
- EF Code First中的主外來鍵約定和一對一、一對多關係的實現
- 淺談Oracle 主外來鍵刪除語法格式Oracle
- Oracle查詢表的外來鍵引用關係Oracle
- 父表修改與外來鍵的關係（主鍵DML與外來鍵的關係）
- 主外來鍵關聯刪除(on delete set null和on delete cascade)deleteNull
- 批量刪除MSSQL 中主外來鍵約束SQL
- sql 查詢 主外來鍵的 一點心得SQL
- Django（15）外來鍵和表關係Django
- 關於主外來鍵表資料提交過程中的一點疑惑
- 關於外來鍵的理解和實驗步驟
- 利用虛擬列實現虛擬刪除的主外來鍵約束
- 生產庫故障分析（主外來鍵和DML效能分析）
- Hibernate-select new Object(Entity obj)非主外來鍵關聯物件查詢Object物件
- [20160704]NULL與主外來鍵問題.txtNull
- 無索引的外來鍵之主表子表DML操作實驗及結論索引
- 主鍵與主鍵索引的關係索引
- 實現 MongoDB 外來鍵關聯MongoDB
- 開發CMP關係的時候，是建立主鍵表CMP關係然後指向外來鍵表CMP，還是反之？
- Entity Framework Code First新增修改及刪除外來鍵關聯實體Framework
- 為什麼在資料倉儲中很少使用主外來鍵約束
- 關於 oracle 外來鍵引用 與 goldengateOracleGo
- 分散式關係型資料庫RadonDB體驗歸來分散式資料庫
- UML 類圖及關係
- [20121020]主外來鍵約束以及NULL問題.txtNull
- Django中ORM外來鍵和表的關係(Django程式設計-4)DjangoORM程式設計
- 【實驗】【外來鍵】小議外來鍵約束對應用程式的影響