列不可为空可推迟 [英] column not null deferrable

问题描述

在Oracle中，仅在提交时才检查延迟的约束.

In Oracle deferred constraints are checked only at the point of commit.

在非空约束的情况下，DEFERRABLE子句的含义是什么? 例如

What is the meaning of DEFERRABLE clause in a case of NOT NULL constraint? For example

create table test(a number not null deferrable, b number);
insert into test(a,b) values (222, 111);
commit;

这些陈述之后，我认为下面的代码会起作用

After these statements I thought the following code would work

update test set a = null where b = 111;
delete test where b = 111;
commit;

但事实并非如此.

两个定义之间有什么区别?

What is the difference between two definitions?

create table test1(a number not null deferrable, b number);
create table test2(a number not null, b number);

推荐答案

此处有两个选项.您需要使用下面显示的命令来设置要在事务内延迟的约束

There are two options here. Either you need to set the constraint to be deferred within the transaction by using the command shown below

SET CONSTRAINTS ALL DEFERRED;

这应该在执行定义的UPDATE语句之前运行.

This should be run before doing the UPDATE statement that you have defined.

或者，您可以在表定义中将约束设置为INITIALLY DEFERRED

Alternatively you can set the constraint to be INITIALLY DEFERRED in the table definition

create table test(a number not null initially deferred deferrable, b number);

完成上述任何一项操作之后，您就应该能够运行问题中的DML.

After doing either of these things, you should then be able to run the DML that you have in the question.

这篇关于列不可为空可推迟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！