oracle可延迟约束

标准规定，约束可以是deferrable或not deferrable（默认）。

 

not deferrable 约束在每一个DML语句后检查；

deferrable 约束可以在每一个insert，delete，或update（即时模式）后立即检查，或者在事务末尾检查（延迟模式）

 

当没有按特定顺序执行数据加载时，这项功能特别有用——它允许先把数据载入子表，然后再装入父表。

另一种用法是在加载不符合某个check约束的数据之后，对其进行适当的更新。

 

语法如下：

[ [not] deferrable [initially {immediate | deferred} ] ]

或

[ [initially {immediate | deferred} ] [not] deferrable ]

 

参考例子：

CREATE TABLE salesman
(
salesman_id_n NUMBER NOT NULL,
salesman_code_s VARCHAR2(2) NOT NULL,
salesman_name_s VARCHAR2(50) NOT NULL,
salesman_status_s CHAR(1) DEFAULT 'Y',
CONSTRAINT chk_salesstatus CHECK (salesman_status_s in ('N', 'Y'))
DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT pk_salesmanprim PRIMARY KEY (salesman_id_n)
);

CREATE TABLE address
(
addr_id_n NUMBER CONSTRAINT pk_addrprimary PRIMARY KEY,
addr_custid_fn NUMBER,
addr_salesmanid_fn NUMBER CONSTRAINT fk_addr_salesman
REFERENCES salesman (salesman_id_n)
ON DELETE CASCADE,
addr_address_s VARCHAR2(60),
addr_type_s VARCHAR2(8) CONSTRAINT chk_addr_type CHECK
(addr_type_s IN ('BILLING', 'SHIPPING')),
addr_city_s VARCHAR2(18) CONSTRAINT nn_addr_city NOT NULL,
addr_state_s CHAR(2),
addr_zip_s VARCHAR2(10) NOT NULL,
addr_country_s CHAR(3)
);

INSERT INTO salesman
(
salesman_id_n,
salesman_code_s,
salesman_name_s,
salesman_status_s
)
VALUES
(23,'02','FAIRFIELD BUGS ASSOCIATION','A');

UPDATE salesman
SET salesman_status_s = 'Y'
WHERE salesman_status_s = 'A';

COMMIT;