ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > oracle 主外键关系及实验

oracle 主外键关系及实验

原创 Oracle 作者：不一样的天空w 时间：2018-06-04 16:41:48 0 删除 编辑
参照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


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31397003/viewspace-2155606/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle+Ogg 归档丢失 重新导数据建立ogg同步步骤
下一篇： oracle tfa 日志收集工具
请登录后发表评论 登录
全部评论
不一样的天空w

注册时间：2016-09-22

  • 博文量
    916
  • 访问量
    1400299

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593