学习oracle约束

管理数据完整性

一 学习目标

  1.实现数据完整性约束
  2.管理完整性约束
  3.从数据字典中获取约束信息

二 保证数据完整性的方法

  1.应用程序代码控制
  2.触发器控制
  3.声明完整性约束

三 约束的类型 (见图)

 

  1.not null    (不能为空)
  2.unique      (值必须唯一)
  3.primary key (not null + unique)
  4.foreign key (该表值必须在外键表中存在)
  5.check       (自己加的条件)
  6.ref         (不熟)

  注：Constraints不但可以建立在Table上，也可以建立在View上。 

四 约束状态

  1.disable novalidate  既不会约束新增数据也不会验证已有数据，等同于disable
  2.disable validate   约束新增数据但不会验证已有数据，启用后禁止DML
  3.enable novalidate  约束新增数据但不会验证已有数据
  4.enable validate    约束新增数据并验证已有数据，等同于enable

  下面举例说明：

  

SQL> create table dept2 as select * from scott.dept;
 
Table created
 
SQL> select * from dept2 order by deptno;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> alter table dept2 add constraint dept2_u1 unique(deptno);
 
Table altered
 
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
 
INDEX_NAME                     TABLE_NAME                     UNIQUENESS
------------------------------ ------------------------------ ----------
DEPT2_U1                       DEPT2                          UNIQUE
 
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
 
CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
DEPT2_U1                       ENABLED  VALIDATED
 
SQL> insert into dept2(deptno) values(10);
 
insert into dept2(deptno) values(10)
 
ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated
 
SQL> select * from dept2 order by deptno;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> alter table dept2 modify constraint dept2_u1 disable novalidate;
 
Table altered
 
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
 
INDEX_NAME                     TABLE_NAME                     UNIQUENESS

------------------------------ ------------------------------ ----------

（disable自动移除索引）

 
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
 
CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
DEPT2_U1                       DISABLED NOT VALIDATED
 
SQL> insert into dept2(deptno) values(10);
 
1 row inserted
 
SQL> select * from dept2 order by deptno;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    10                
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> alter table dept2 modify constraint dept2_u1 enable novalidate;
 
alter table dept2 modify constraint dept2_u1 enable novalidate
 
ORA-02299: cannot validate (SYSTEM.DEPT2_U1) - duplicate keys found
 （因为enable会去创建唯一性索引，而已有数据deptno存在重复数据10，所以这里不能enable）

SQL> delete from dept2 where deptno=10 and dname is null;
 
1 row deleted
 
SQL> alter table dept2 modify constraint dept2_u1 enable novalidate;
 
Table altered
 
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
 
INDEX_NAME                     TABLE_NAME                     UNIQUENESS
------------------------------ ------------------------------ ----------

DEPT2_U1                       DEPT2                          UNIQUE

（enable会自动创建唯一性索引）


SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
 
CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
DEPT2_U1                       ENABLED  NOT VALIDATED
 
SQL> insert into dept2(deptno) values(10);
 
insert into dept2(deptno) values(10)
 
ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated
 
SQL> alter table dept2 modify constraint dept2_u1 disable validate;
 
Table altered
 
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
 
INDEX_NAME                     TABLE_NAME                     UNIQUENESS
------------------------------ ------------------------------ ----------
 
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
 
CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
DEPT2_U1                       DISABLED VALIDATED
 
SQL> insert into dept2(deptno) values(10);
 
insert into dept2(deptno) values(10)
 

ORA-25128: No insert/update/delete on table with constraint (SYSTEM.DEPT2_U1) disabled and validated

（disable validate后禁止DML）


SQL> select * from dept2 order by deptno;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> alter table dept2 modify constraint dept2_u1 enable validate;
 
Table altered
 
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
 
INDEX_NAME                     TABLE_NAME                     UNIQUENESS
------------------------------ ------------------------------ ----------
DEPT2_U1                       DEPT2                          UNIQUE
 
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
 
CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
DEPT2_U1                       ENABLED  VALIDATED
 
SQL> insert into dept2(deptno) values(10);
 
insert into dept2(deptno) values(10)
 
ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated
 
SQL> select * from dept2 order by deptno;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

 

五 推迟约束
 
 当前事务的Constraint Checks全部推迟

 SET CONSTRAINTS ALL DEFERRED;
 SET CONSTRAINT XXX DEFERRED;

 当前会话的Constraint Checks．

 ALTER SSSSION SET CONSTRAINTS ALL deferred;
 ALTER SSSSION SET CONSTRAINT xxx deferred;

  2.session级别修改约束检查项：
    ALTER SESSION
    SET CONSTRAINT[S] =
    {IMMEDIATE|DEFERRED|DEFAULT}

        SET CONSTRAINT | CONSTRAINTS
    {constraint |ALL }
    {IMMEDIATE|DEFERRED}

六  创建约束

1.建表时定义约束：
例：sql
约束类型 [CONSTRAINT constraint]
            {[NOT] NULL
            |UNIQUE      [USING INDEX index_clause]
            |PRIMARY KEY [USING INDEX index_clause]
            |REFERENCES  [schema.]table [(column)]
                     [ON DELETE CASCADE]
            |CHECK       (condition)
            }
            约束状态 :==
            [NOT DEFERRABLE|DEFERRABLE [INITIALLY             {IMMEDIATE|DEFERRED}]
            ]
            [DISABLE|ENABLE [VALIDATE|NOVALIDATE]]
2.建表后增加约束：

七  使用EXCEPTIONS  TABLE

1. 如果异常未创建, 运行脚本 utlexcpt.sql：
   

SQL> @ e:\oracle\rdbms\admin\utlexcpt.sql

表已创建。

SQL> desc exceptions
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ROW_ID                                             ROWID
 OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 CONSTRAINT                                         VARCHAR2(30)

2. 使用异常表

 

SQL> alter table dept2 modify constraint dept2_u1 disable novalidate;

表已更改。

SQL> insert into dept2 (deptno) values(10);

已创建 1 行。

SQL> alter table dept2
  2  enable validate constraint dept2_u1
  3  exceptions into exceptions;
alter table dept2
*
第 1 行出现错误:
ORA-02299: 无法验证 (SCOTT.DEPT2_U1) - 找到重复关键字

3. 使用子查询查找异常表记录的非法记录:
 

SQL> select rowid,dept2.* from dept2
  2  where rowid in (select row_id from exceptions) for update;

ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAMlpAAEAAAAHkAAA         10 ACCOUNTING     NEW YORK
AAAMlpAAEAAAAHlAAB         10

SQL> update dept2
  2  set deptno = 50
  3  where rowid='AAAMlpAAEAAAAHlAAB';

已更新 1 行。

SQL> commit;

提交完成。

4. How to Identify Row Violation (continued)
 

5.  Truncate the EXCEPTIONS table and reenable the constraint:
SQL> TRUNCATE TABLE exceptions;
Statement processed.
SQL> ALTER TABLE hr.employees
  2  ENABLE VALIDATE CONSTRAINT employee_dept_id_fk
  3  EXCEPTIONS INTO system.exceptions;
Statement processed.

八   从数据字典获取约束信息

DBA_CONSTRAINTS
DBA_CONS_COLUMNS

SQL> SELECT constraint_name, constraint_type, deferrable,
  2         deferred, validated
  3  FROM   dba_constraints
  4  WHERE  owner='HR'
  5  AND    table_name='EMPLOYEE';
CONSTRAINT_NAME   C     DEFERRABLE       DEFERRED    VALIDATED
----------------  -     ---------------  -----------    ----------
EMPLOYEE_DEPT..   R     DEFERRABLE       DEFERRED    VALIDATED
EMPLOYEE_ID_PK    P     DEFERRABLE       IMMEDIATE    VALIDATED
SYS_C00565        C     NOT DEFERRABLE   IMMEDIATE    VALIDATED
3 rows selected.

要在HR下找到EMPLOYEE的外键以及关联的主键, 使用以下查询:

SQL> SELECT c.constraint_name AS "Foreign Key",
  2         p.constraint_name AS "Referenced Key",
  3         p.constraint_type,
  4         p.owner,
  5         p.table_name
  6  FROM   dba_constraints c, dba_constraints p
  7  WHERE  c.owner='HR'
  8  AND    c.table_name='EMPLOYEE'
  9  AND    c.constraint_type='R'
  10 AND    c.r_owner=p.owner
  11 AND    c.r_constraint_name = p.constraint_name;
Foreign Key       Referenced Key  C  OWNER       TABLE_NAME    
------------      --------------  -  ----------  ----------
EMPLOYEES_DEPT..   DEPT_PK        P  HR          DEPARTMENT
1 row selected.

------------------------------------

made by dylan.