oracle deferrable 笔记



DEFERRABLE Clause The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

• Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

  If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

• Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

You cannot alter the deferrability of a constraint. That is, whether you specify either of these parameters, or make the constraint NOT DEFERRABLE implicitly by specifying neither of them, you cannot specify this clause in an ALTER TABLE statement. You must drop the constraint and re-create it.

--创建表默认non deferred
SQL> create table games(scores number check (scores>0));

Table created.

--插入数据后，直接报错，因为是non deferred
SQL> insert into games values(-1);
insert into games values(-1)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0037157) violated

--在创建一个表约束是deferrable的
SQL> create table games_deferrable(
  2  scores number,
  3  constraint ck_scores_def check(scores>0)
  4  initially deferred deferrable)
  5
SQL> /

Table created.

--插入数据不会立刻报错
SQL> insert into games_deferrable values(-1);

1 row created.

--提交事务立即报错
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.CK_SCORES_DEF) violated

--查看games的约束名称
SQL> select constraint_name
  2  from user_constraints
  3  where table_name='GAMES';

CONSTRAINT_NAME
------------------------------
SYS_C0037157

--设置为deferred报错，因为non deferred不允许设置
SQL> SET CONSTRAINT SYS_C0037157 DEFERRED;
SET CONSTRAINT SYS_C0037157 DEFERRED
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable

--设置games_deferrable的约束为immediate
SQL> SET CONSTRAINT CK_SCORES_DEF IMMEDIATE;

Constraint set.

--变成了non deferrable，插入即报错
SQL> INSERT INTO GAMES_DEFERRABLE VALUES(-1);
INSERT INTO GAMES_DEFERRABLE VALUES(-1)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_SCORES_DEF) violated

--在创建一个表也是deferrable的
SQL> CREATE TABLE GAMES_DEFERRABLE2(
  2  SCORES NUMBER,
  3  CONSTRAINT CK_SCORES_DEF2 CHECK(SCORES>0)
  4  INITIALLY DEFERRED DEFERRABLE)
  5  /

Table created.

--插入不报错
SQL> INSERT INTO GAMES_DEFERRABLE2 VALUES(-1);

1 row created.
--commit时报错
SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.CK_SCORES_DEF2) violated

--设置所有约束为immediate
SQL> SET CONSTRAINTS ALL IMMEDIATE;

Constraint set.

--插入即报错
SQL> INSERT INTO GAMES_DEFERRABLE VALUES(-1);
INSERT INTO GAMES_DEFERRABLE VALUES(-1)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_SCORES_DEF) violated

--插入即报错
SQL> INSERT INTO GAMES_DEFERRABLE2 VALUES(-1);
INSERT INTO GAMES_DEFERRABLE2 VALUES(-1)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_SCORES_DEF2) violated

--约束设置为deferred
SQL> SET CONSTRAINT CK_SCORES_DEF,CK_SCORES_DEF2 DEFERRED
  2  /

Constraint set.

--插入不报错
SQL> INSERT INTO GAMES_DEFERRABLE VALUES(-1);

1 row created.

--插入不报错
SQL> INSERT INTO GAMES_DEFERRABLE2 VALUES(-1);

1 row created.

--提交事务立即报错
SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.CK_SCORES_DEF) violated

SQL>--alter table 不可以修改为derrerred