Oracle DBA课程系列笔记（14）-白红宇

第十四章：约束（ 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 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）时检查

本文转自客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/791797，如需转载请自行联系原作者