首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

ORACLE约束的属性

作者：sdx3418153 | 来源：互联网 | 2014-07-11 17:33
ORACLE约束的属性今天处理了一个由于约束插入数据失败的问题，处理时感到有些吃力，三天不练手生啊。在这里回忆一下。Oracle数据库Constraint约束的四对属性：Deferrable/notdeferrable,Deferred/immediate,...SyntaxHi

ORACLE约束的属性
 
今天处理了一个由于约束插入数据失败的问题，处理时感到有些吃力，三天不练手生啊。在这里回忆一下。
Oracle数据库Constraint约束的四对属性：Deferrable/not deferrable, Deferred/immediate, enable/disable, validate/novalidate。
  www.2cto.com  
准备一下环境：
SQL> create table empx as select * from emp;
 
SQL> create table deptx as select * from dept;
 
SQL> alter table empx add constraint pk_empx primary key(empno);
 
SQL> alter table deptx add constraint pk_deptx primary key(deptno);
 
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno);
 
SQL> select * from deptx;
 
[plain] 
   DEPTNO DNAME                   LOC  
--------- ------------------------------------------ ---------------------------------------    www.2cto.com  
10 ACCOUNTING                     NEW YORK  
20 RESEARCH                   DALLAS  
30 SALES                      CHICAGO  
40 OPERATIONS                     BOSTON  
 
SQL> select * from empx;
 
[plain] 
     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  
  
14 rows selected.  
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name=&＃39;EMPX&＃39;;
 
[plain] 
CONSTRAINT_NAME      COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED  
------------------------ ---------------- ------------------------ ---------------- ---------------- ------------  
PK_EMPX                   ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED  
FK_DEPTX                  ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED  
 
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50);
insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found
 
这里通过emp和dept表创建了两张表empx和deptx,并在empx表上创建了外键约束fk_deptx。
 
1.Deferrable,not deferrable(default value)
 
1)这对属性是定义是否可以延时验证,deferrable是指作验证时机,如果在commit的时check为deferrable,否则为immediate .只有在设置Deferrable才可以设置另一属性Deferred,immediate.
  www.2cto.com  
2)这对属性是在创建的constraint的时候定义的,不能被修改.
 
notice:如果建立了Deferrable的uk或pk,只会建立相应的nonuniquce index,而不会建立uniquce index
 
SQL> alter table empx drop constraint fk_deptx;
 
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable;
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name=&＃39;EMPX&＃39;;
 
[plain] 
CONSTRAINT_NAME      COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED  
------------------------ ---------------- ------------------------ ---------------- ---------------- ------------  
PK_EMPX                   ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED  
FK_DEPTX                  ENABLED          DEFERRABLE       IMMEDIATE        VALIDATED  
 
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50);
insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found
 
在会话级设置：
SQL> set constraint FK_DEPTX deferred;
 
SQL>  select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name=&＃39;EMPX&＃39;;
 
[plain] 
CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED            VALIDATED  
---------------- ---------------- ------------------------ ---------------- --------------------------- ----------------  
PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE           VALIDATED  
FK_DEPTX              ENABLED          DEFERRABLE       IMMEDIATE           VALIDATED  
 
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50);
 
1 row created.
 
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found
 
单独deferrable的含义就是是否允许约束检查延后进行。单独设置deferrable为deferrable之后，约束检查延后是不可以直接使用的，要配合deferred参数，如果该参数是immediate，那么约束还是在DML的时候进行应用。如果deferred参数设置为deferred，约束就是在事务commit提交的时候应用，出现错误就连带回滚rollback整个事务。
2.Deferred,immediate(default value)
  www.2cto.com  
deferred属性是配合deferrable属性使用的。当deferrable设置为deferrable之后，可以通过set constraints在会话级进行deferred属性的设置，来确定约束应用时点。
定义约束是使用initially关键字来确定约束的deferred属性取值。
 
SQL> alter table empx drop constraint fk_deptx;
 
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred;
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name=&＃39;EMPX&＃39;;
 
[plain] 
CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED            VALIDATED  
---------------- ---------------- ------------------------ ---------------- --------------------------- ----------------  
PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE           VALIDATED  
FK_DEPTX              ENABLED          DEFERRABLE       DEFERRED            VALIDATED  
 
SQL>  insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50);
1 row created.
 
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found
 
可以通过创建contraint时指定Deferred值，也可以通过会话级别的语句SET CONSTRAINT(s)来设置（如上面的set constraint FK_DEPTX deferred）。
 
当属性DEFERRABLE和DEFERRED的值分别为DEFERRABLE和DEFERRED时，在事务提交时才验证，如果验证没通过，则报错，事务回滚。
 
3. novalidate, validate(default value)
定义是否对表中已经存在的数据作检查。
 
删除约束并插入一条脏数据：
SQL> alter table empx drop constraint fk_deptx;
 
Table altered.
SQL>  insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50);
 
1 row created.
 
SQL> commit;
  www.2cto.com  
Commit complete.
 
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno);
alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred
                                *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found
 
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred;
alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred
                                *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found
 
语句中不带validate属性参数，默认创建的是validate的约束。由于有脏数据的存在，不管是创建延时还是非延时的约束都不成功。
 
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) novalidate;
 
Table altered.
 
创建novalidate的约束成功。
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name=&＃39;EMPX&＃39;;
 
[plain] 
CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED  
---------------- ---------------- ------------------------ ---------------- ---------------- ----------------  
PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED  
FK_DEPTX              ENABLED          NOT DEFERRABLE   IMMEDIATE        NOT VALIDATED  
 
SQL> select * from empx;
[plain] 
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  
 8000 TOM                 SALESMAN              7839 15-JUL-12         2000            50  
 
有一条脏数据存在，看看能不能再增加一条：
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50);
insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50)
*  www.2cto.com  
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found
不能增加违反约束的数据，novalidate的约束只是对原来的数据库不做验证，但对新插入的数据还是要进行验证的。
 
4. disable, enalbe(default value)
 
启用和禁用constraint.如果在新建pk和uk时定义了disable,将不建立相应的索引.
SQL> alter table empx disable constraint fk_deptx;
Table altered.
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name=&＃39;EMPX&＃39;;
 
[plain] 
CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED  
---------------- ---------------- ------------------------ ---------------- ---------------- ----------------  
PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED  
FK_DEPTX              DISABLED         NOT DEFERRABLE   IMMEDIATE        NOT VALIDATED  
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,&＃39;TOM&＃39;,&＃39;SALESMAN&＃39;,7839,sysdate,2000,50);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
禁用了约束后，脏数据也能插入成功。
 
SQL> alter table empx enable constraint fk_deptx;
alter table empx enable constraint fk_deptx
                                   *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name=&＃39;EMPX&＃39;;
 
[plain] 
CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED    www.2cto.com  
---------------- ---------------- ------------------------ ---------------- ---------------- ----------------  
PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED  
FK_DEPTX              DISABLED         NOT DEFERRABLE   IMMEDIATE        NOT VALIDATED  
 
SQL> alter table empx enable novalidate constraint fk_deptx;
 
Table altered.
 
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name=&＃39;EMPX&＃39;;
 
[plain] 
CONSTRAINT_NAME  COND         STATUS           DEFERRABLE       DEFERRED         VALIDATED  
---------------- ---------------- ------------------------ ---------------- ---------------- ----------------  
PK_EMPX               ENABLED          NOT DEFERRABLE   IMMEDIATE        VALIDATED  
FK_DEPTX              ENABLED          NOT DEFERRABLE   IMMEDIATE        NOT VALIDATED  
 
如果有脏数据，启用约束时要同时设置novalidate。
  www.2cto.com  
由上面的实验得知，默认情况下，Oracle constraint是不开启延迟约束和原有数据保留验证的。
 
下列情况下可以使用这些特性：
1）批量数据导入、物化视图刷新的时候，事务量很大，而且存在数据规律前后颠倒的情况
此时，如果开启着立即约束应用的开关，可能存在一些暂时性的约束不满足的情况，从而影响到整个系统的运行。开启约束延迟验证，就可以帮助我们解决这个问题；
2）在历史数据移植
历史数据存在不规则的情况，很多时候难以满足我们新系统的数据完整性要求。可以使用not validate的方式，对历史数据不进行约束控制，而只针对新数据开启验证。
 
 
作者 ilovemilk

推荐阅读
  • go

    每天收获一点点Hadoop概述

    一、Hadoop来历Hadoop的思想来源于Google在做搜索引擎的时候出现一个很大的问题就是这么多网页我如何才能以最快的速度来搜索到，由于这个问题Google发明 ... [详细]
  • go

    Oracle分析函数first_value()和last_value()的用法及原理

    Oracle分析函数first_value()和last_value()的用法及原理
    本文介绍了Oracle分析函数first_value()和last_value()的用法和原理，以及在查询销售记录日期和部门中的应用。通过示例和解释，详细说明了first_value()和last_value()的功能和不同之处。同时，对于last_value()的结果出现不一样的情况进行了解释，并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]
  • command

    VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

    本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]
  • include

    第六周周赛题解

    A题这题贼水，直接暴力就可以了。用个bool数组记录一下，如果某一天，当前剩下的最大的出现了的话，就输出一段。1#include<stdio.h>2intn;3boolvi ... [详细]
  • import

    python与开源gis电子书_python 与开源Gis 书本知识点测试

    #-*-coding:utf-8-*-print(upython与开源QGis课题研究组)#print(汉字)##创建矢量数据文件#try:fromosgeoimporto ... [详细]
  • post

    dump备份不影响数据库正常运行,mysqldump数据库备份

    本文目录一览：1、\mysybase.dump对数据库正常使用有影响吗 ... [详细]
  • post

    winyyy.sys,hcpidesk.sys,mtlrd.sys,uldfhjfh.sys,servets.exe等1：

    本文主要介绍关于人工智能,shell的知识点，对【winyyy.sys,hcpidesk.sys,mtlrd.sys,uldfhjfh.sys,servets.exe等1】和【】有兴趣的朋友可以看下由 ... [详细]
  • include

    开发笔记:2D阵列选择排序

    篇首语：本文由编程笔记#小编为大家整理，主要介绍了2D阵列选择排序相关的知识，希望对你有一定的参考价值。所以 ... [详细]
  • jar

    [更新幻灯]1剔除“伪创新”和“无领域”的领域驱动设计

    [更新幻灯]1剔除“伪创新”和“无领域”的领域驱动设计
    如果有人不了解人体的内部结构，就自称医生，声称自己能给人开腹割掉发炎的阑尾，甚至还能开胸给冠心病人做心脏搭桥，你信吗 ... [详细]
  • c语言

    学习SLAM的女生，很酷

    学习SLAM的女生，很酷
    本文介绍了学习SLAM的女生的故事，她们选择SLAM作为研究方向，面临各种学习挑战，但坚持不懈，最终获得成功。文章鼓励未来想走科研道路的女生勇敢追求自己的梦想，同时提到了一位正在英国攻读硕士学位的女生与SLAM结缘的经历。 ... [详细]
  • text

    WinPythonHadoop在Win10上安装教程

    本文介绍了在Win10上安装WinPythonHadoop的详细步骤，包括安装Python环境、安装JDK8、安装pyspark、安装Hadoop和Spark、设置环境变量、下载winutils.exe等。同时提醒注意Hadoop版本与pyspark版本的一致性，并建议重启电脑以确保安装成功。 ... [详细]
  • metadata

    Hyperledger Fabric外部链码构建与运行的开发笔记

    本文介绍了Hyperledger Fabric外部链码构建与运行的相关知识，包括在Hyperledger Fabric 2.0版本之前链码构建和运行的困难性，外部构建模式的实现原理以及外部构建和运行API的使用方法。通过本文的介绍，读者可以了解到如何利用外部构建和运行的方式来实现链码的构建和运行，并且不再受限于特定的语言和部署环境。 ... [详细]
  • post

    的错误消息：

    ZSI.generate.Wsdl2PythonError: unsupported local simpleType restriction ... [详细]
  • post

    Perl的测试框架Test::Base简介及使用方法

    本文介绍了Perl的测试框架Test::Base，它是一个数据驱动的测试框架，可以自动进行单元测试，省去手工编写测试程序的麻烦。与Test::More完全兼容，使用方法简单。以plural函数为例，展示了Test::Base的使用方法。 ... [详细]
  • tree

    推荐系统遇上深度学习(十七）详解推荐系统中的常用评测指标

    推荐系统遇上深度学习(十七）详解推荐系统中的常用评测指标
    原创：石晓文小小挖掘机2018-06-18笔者是一个痴迷于挖掘数据中的价值的学习人，希望在平日的工作学习中，挖掘数据的价值， ... [详细]
author-avatar
sdx3418153
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有