Oracle DBA课程系列笔记（14）

最新推荐文章于 2022-05-08 00:46:45 发布

weixin_34306593

最新推荐文章于 2022-05-08 00:46:45 发布

阅读量123

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/tiany/791797

版权

第十四章：约束（ Constraint）

1、约束的功能：通过一些强制性商业规则，保证数据的完整性、一致性

2、约束的类别：
    1） not null
    2） check
    3） unique
    4） primary key （not null + unique）
    5） foreign key （reference）
3、约束的状态

     1） enable validate（默认）：约束会立刻生效，并对新旧数据都要符合约束


11:43:19 SQL> alter table emp1
11:44:19   2   enable validate constraint pk_emp1;
alter table emp1
*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.PK_EMP1) - primary key violated
-----新旧记录都必须符合约束

     2） enable novalidate ：约束会立刻生效，只检查新的数据，旧的数据可以不符合约束（注意对primary key 和unique，必须先建立非唯一性索引）

     11:37:46 SQL> create table emp1 as select * from emp;

Table created.

11:37:51 SQL> select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

11:38:04 SQL> update emp1 set empno=7788 where ename='KING';

1 row updated.
11:39:00 SQL> select * from emp1 WHERE EMPNO=7788;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 KING       PRESIDENT            17-NOV-81       5000                    10

11:39:10 SQL>

---------添加primary key ，到enable novalidate
11:39:10 SQL> alter table emp1 add constraint
11:39:42 2 pk_emp1 primary key (empno) enable novalidate;
pk_emp1 primary key (empno) enable novalidate
*
ERROR at line 2:
ORA-02437: cannot validate (SCOTT.PK_EMP1) - primary key violated
---------原因：在建立primary key 它需要建立unique index

---------在empno 上建立索引（非唯一性索引）
11:40:06 SQL> create index emp1_empno_ind on emp1(empno) tablespace indexes;

Index created.

11:42:17 SQL> alter table emp1 add constraint
11:42:20 2 pk_emp1 primary key (empno) using index enable novalidate ;

Table altered.

11:42:29 SQL>

--------新的记录必须符合约束，旧的不检查

11:42:58 SQL> update emp1 set empno=7788 where ename='FORD';
update emp1 set empno=7788 where ename='FORD'
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP1) violated

     3） disable validate：约束失效，但不能做DML操作
     4） disable novalidate：约束失效，不检查任何数据（等于禁用约束）



4、修正约束（将违背约束的记录插入到exceptions表里）

      1）11:47:07 SQL> @?/rdbms/admin/utlexpt1
11:47:09 SQL> Rem
11:47:09 SQL> Rem $Header: utlexpt1.sql 24-jun-99.07:59:18 echong Exp $
11:47:09 SQL> Rem
11:47:09 SQL> Rem utlexpt1.sql
11:47:09 SQL> Rem
11:47:09 SQL> Rem Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
11:47:09 SQL> Rem
11:47:09 SQL> Rem    NAME
11:47:09 SQL> Rem        utlexpt1.sql - <one-line expansion of the name>
11:47:09 SQL> Rem
11:47:09 SQL> Rem    DESCRIPTION
11:47:09 SQL> Rem        <short description of component this file declares/defines>
11:47:09 SQL> Rem
11:47:09 SQL> Rem    NOTES
11:47:09 SQL> Rem        <other useful comments, qualifications, etc.>
11:47:09 SQL> Rem
11:47:09 SQL> Rem    MODIFIED   (MM/DD/YY)
11:47:09 SQL> Rem    echong      06/24/99 - rename
11:47:09 SQL> Rem    echong      06/05/98 - exceptions table with urowid type
11:47:09 SQL> Rem    echong      06/05/98 - Created
11:47:09 SQL> Rem
11:47:09 SQL>
11:47:09 SQL> create table exceptions(row_id urowid,
11:47:09   2                          owner varchar2(30),
11:47:09   3                          table_name varchar2(30),
11:47:09   4                          constraint varchar2(30));

Table created.

11:47:09 SQL>
11:47:09 SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
ADMIN_EXT_EMPLOYEES            TABLE
EMPLOYEES                      TABLE
TEST                           TABLE
EMP1                           TABLE
EXCEPTIONS                     TABLE

9 rows selected.

11:47:18 SQL>
11:48:04 SQL> alter table emp1
11:48:06 2 enable validate constraint pk_emp1 exceptions into exceptions;
alter table emp1
*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.PK_EMP1) - primary key violated

---------把违背约束的记录插入到exceptions 表里

11:48:10 SQL> select * from exceptions;
ROW_ID                         OWNER                TABLE_NAME                     CONSTRAINT
------------------------------ -------------------- ------------------------------ ------------------------------
AAAM0/AAEAAAAGcAAI             SCOTT                EMP1                           PK_EMP1
AAAM0/AAEAAAAGcAAH             SCOTT                EMP1                           PK_EMP1

11:48:59 SQL> delete from emp1
11:50:02 2 where rowid in (select row_id from exceptions);

2 rows deleted.

11:50:25 SQL> alter table emp1
11:50:29 2 enable validate constraint pk_emp1;

Table altered.

11:50:38 SQL>

5、延迟性约束（约束只在提交事务时检查）

11:53:27 SQL> alter table dept1
11:53:38 2 add constraint pk_dept1 primary key (deptno);

Table altered.

11:53:58 SQL> alter table emp1
11:54:00 2 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) deferrable;

Table altered.

----------deferrable 可延迟（默认是immediate ，非 deferr 延迟）

11:54:08 SQL> set constraint fk_emp1 deferred;

Constraint set.
---------将约束fk_emp1 进入延迟状态
11:55:04 SQL> select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

12 rows selected.

11:55:41 SQL> select * from dept1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

11:55:44 SQL> update emp1 set deptno=50 where empno=7900;

1 row updated.

11:56:12 SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_EMP1) violated - parent key not found

11:56:28 SQL> select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

12 rows selected.

11:56:39 SQL>

---------插入记录只在提交（commit）时检查

转载于:https://blog.51cto.com/tiany/791797

weixin_34306593

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle DBA课程系列笔记（14）

第十四章：约束（ Constraint）1、约束的功能：通过一些强制性商业规则，保证数据的完整性、一致性2、约束的类别： 1） not null 2） check 3） unique 4） primary key （not null + unique） 5） foreign key （reference） ...
复制链接

扫一扫