6赞

ORACLE约束的属性

作者：小白也坚强_177 | 2021-08-13 22:50

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='EMPX';

[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,'TOM','SALESMAN',7839,sysdate,2000,50);

insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',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='EMPX';

[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,'TOM','SALESMAN',7839,sysdate,2000,50);

insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',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='EMPX';

[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,'TOM','SALESMAN',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='EMPX';

[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,'TOM','SALESMAN',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,'TOM','SALESMAN',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='EMPX';

[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,'TOM','SALESMAN',7839,sysdate,2000,50);

insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,'TOM','SALESMAN',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='EMPX';

[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,'TOM','SALESMAN',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='EMPX';

[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='EMPX';

[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

推荐阅读

程序员
我们如何将gRPC与Flatbuffers一起使用？

如何解决《我们如何将gRPC与Flatbuffers一起使用？》经验，为你挑选了2个好方法。 ... [详细]
程序员
SummerNote insertHtml

如何解决《SummerNoteinsertHtml》经验，为你挑选了2个好方法。 ... [详细]
程序员
GLFW输入状态

如何解决《GLFW输入状态》经验，为你挑选了1个好方法。 ... [详细]
程序员
如果有一个意图过滤器,为什么要检查Intent在BroadcastReceiver中的操作

如何解决《如果有一个意图过滤器,为什么要检查Intent在BroadcastReceiver中的操作》经验，为你挑选了1个好方法。 ... [详细]
程序员
如何处理Mockito的"任何其他价值"？

如何解决《如何处理Mockito的"任何其他价值"？》经验，为你挑选了1个好方法。 ... [详细]
程序员
Rails - 无法找到类型为'application/javascript'的文件'jquery_ujs'

如何解决《Rails-无法找到类型为'application/javascript'的文件'jquery_ujs'》经验，为你挑选了0个好方法。 ... [详细]
程序员
ios如何添加inputAccessoryView？

如何解决《ios如何添加inputAccessoryView？》经验，为你挑选了1个好方法。 ... [详细]
程序员
Android工作室不生成签名的apk

如何解决《Android工作室不生成签名的apk》经验，为你挑选了2个好方法。 ... [详细]
程序员
如何使用JSTL对HTML标签进行转义

如何解决《如何使用JSTL对HTML标签进行转义》经验，为你挑选了1个好方法。 ... [详细]
程序员
在Redis-py中使用锁

如何解决《在Redis-py中使用锁》经验，为你挑选了0个好方法。 ... [详细]
程序员
元组解包顺序更改分配的值

如何解决《元组解包顺序更改分配的值》经验，为你挑选了3个好方法。 ... [详细]
程序员
如何在Mac上创建立体OpenGL视图？

如何解决《如何在Mac上创建立体OpenGL视图？》经验，为你挑选了0个好方法。 ... [详细]
程序员
停止固定背景图像滚动到一定高度

如何解决《停止固定背景图像滚动到一定高度》经验，为你挑选了0个好方法。 ... [详细]
程序员
尝试应用AttributeConverter时出错

如何解决《尝试应用AttributeConverter时出错》经验，为你挑选了1个好方法。 ... [详细]
程序员
是否在创建其继承类的新对象时创建了基类的对象？

如何解决《是否在创建其继承类的新对象时创建了基类的对象？》经验，为你挑选了0个好方法。 ... [详细]
程序员
在我的项目中构建和重用android开源Dialer源代码

如何解决《在我的项目中构建和重用android开源Dialer源代码》经验，为你挑选了0个好方法。 ... [详细]
程序员
如何在python中查询AWS DynamoDB？

如何解决《如何在python中查询AWSDynamoDB？》经验，为你挑选了2个好方法。 ... [详细]
程序员
sha1不能使用密码加密

如何解决《sha1不能使用密码加密》经验，为你挑选了1个好方法。 ... [详细]
程序员
从python脚本返回值到shell脚本

如何解决《从python脚本返回值到shell脚本》经验，为你挑选了1个好方法。 ... [详细]
程序员
android mediaprojection截图包含黑框

如何解决《androidmediaprojection截图包含黑框》经验，为你挑选了1个好方法。 ... [详细]

小白也坚强_177

这个屌丝很懒，什么也没留下！

关注作者

Tags | 热门标签

RankList | 热门文章