oracle中的默认约束,oracle中的约束

oracle 中约束有如下分类：

1、not deferrable(默认)

2、deferrable:

①initially immediate：每条语句执行结束时检查约束

②initially deferred：事务结束时才检查约束(或者设置了set constraint immediate时检查)

not deferrable和deferrable initially immediate 效果一样[@more@]

实际环境测试结果如下：

操作系统：AIX 5.3

oracle版本：9.2.0.8

SQL> create table a (

2 x int constraint check_x check ( x > 0 )

3 　　deferrable

4 　　initially immediate,

5 y int constraint check_y check ( y > 0 )

6 　　deferrable

7 　　initially deferred

8 );

Table created

SQL> insert into a values (1,1);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from a;

X Y

------------- ---------------

1 1

SQL> insert into a values (-1,1);

insert into a values (-1,1)

ORA-02290: check constraint (SAN.CHECK_X) violated

//立刻检查约束

SQL> select * from a;

X Y

---------- ----------

1 1

//回滚

SQL> insert into a values (1,-1);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back

ORA-02290: check constraint (SAN.CHECK_Y) violated

//事务结束时检查

SQL> set constraints all deferred;

Constraints set

//手工设置为延迟检查约束

SQL> insert into a values (-1,1);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back

ORA-02290: check constraint (SAN.CHECK_X) violated

SQL> set constraints all immediate;

//手工设置为立刻检查约束

Constraints set

SQL> insert into a values (1,-1);

insert into a values (1,-1)

ORA-02290: check constraint (SAN.CHECK_Y) violated