学习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. 使用子查询查找异常表记录的非法记录:

4. How to Identify Row Violation (continued)

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;

提交完成。

posted on 2012-08-18 23:07 地心引力阅读(1325) 评论(0) 编辑收藏

常用链接

留言簿

随笔档案

搜索

最新评论

阅读排行榜

评论排行榜


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻 Chat2DB C++博客博问管理