首页 > IT知识 > Oracle约束的状态及验证机制

Oracle约束的状态及验证机制

2017年08月15日10:37:10来源:海文国际         95
分享到:
IT知识文章: 

一、Oracle约束的状态

Oracle完整性约束的状态有4种,分别是ENABLE、DISABLE、VALIDATE、NOVALIDATE。

ENABLE表示Oracle将检查要插入或更新的数据库中的数据是否符合约束;

DISABLE表示表中可以存放违反约束的行;

VALIDATE表示数据库验证表中的已存在数据是否符合约束;

NOVALIDATE表示数据库不验证表中已存在数据是否符合约束。

Oracle默认约束状态为ENABLE、VALIDATE。

下面看Oracle官方给出的汇总:

ModifiedDataExistingDataSummary

ENABLE

VALIDATE

Existingandfuturedatamustobeytheconstraint.Anattempttoapplyanewconstrainttoapopulatedtableresultsinanerrorifexistingrowsviolatetheconstraint.

ENABLE

NOVALIDATE

Thedatabasecheckstheconstraint,butitneednotbetrueforallrows.Thus,existingrowscanviolatetheconstraint,butnewormodifiedrowsmustconformtotherules.

DISABLE

VALIDATE

Thedatabasedisablestheconstraint,dropsitsindex,andpreventsmodificationoftheconstrainedcolumns.

DISABLE

NOVALIDATE

Theconstraintisnotcheckedandisnotnecessarilytrue.

下面使用实例测试各状态:

创建测试表

zx@ORA11G>createtablet1(idnumber,namevarchar2(10),addressvarchar2(10));

Tablecreated.

zx@ORA11G>insertintot1values(1,'zx','hb');

1rowcreated.

zx@ORA11G>insertintot1values(1,'zq','jx');

1rowcreated.

zx@ORA11G>insertintot1values(2,'wl','sd');

1rowcreated.

zx@ORA11G>commit;

Commitcomplete.

1、测试ENABLE、VALIDATE状态

zx@ORA11G>altertablet1addconstraintt1_ukunique(id);

altertablet1addconstraintt1_ukunique(id)

*

ERRORatline1:

ORA-02299:cannotvalidate(ZX.T1_UK)-duplicatekeysfound

因为id列中有重复值,此时创建约束t1_uk的状态为ENABLE、VALIDATE会验证表中已存在的数据,所以创建约束不成功。删除表中的重复数据再次创建约束即可成功。

zx@ORA11G>deletefromt1whereid=1andname='zq';

1rowdeleted.

zx@ORA11G>commit;

Commitcomplete.

zx@ORA11G>altertablet1addconstraintt1_ukunique(id);

Tablealtered.

zx@ORA11G>selecttable_name,constraint_name,constraint_type,deferrable,status,validatedfromuser_constraintswheretable_name='T1';

TABLE_NAMECONSTRAINT_NAMECDEFERRABLESTATUSVALIDATED

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

T1T1_UKUNOTDEFERRABLEENABLEDVALIDATED

创建完成后再次插入id=1的数据即会报错,说明约束状态为ENABLE

zx@ORA11G>insertintot1values(1,'zq','jx');

insertintot1values(1,'zq','jx')

*

ERRORatline1:

ORA-00001:uniqueconstraint(ZX.T1_UK)violated

2、测试ENABLE、DISABLED状态

zx@ORA11G>select*fromt1;

IDNAMEADDRESS

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

1zxhb

2wlsd

1zqjx

zx@ORA11G>altertablet1addconstraintt1_ukunique(id)enablenovalidate;

altertablet1addconstraintt1_ukunique(id)enablenovalidate

*

ERRORatline1:

ORA-02299:cannotvalidate(ZX.T1_UK)-duplicatekeysfound

直接创建unique约束报错,因为有重复值。但先在id列上创建索引,然后创建unique约束即可成功。

zx@ORA11G>createindexidx_t_idont1(id);

Indexcreated.

zx@ORA11G>altertablet1addconstraintt1_ukunique(id)usingindexidx_t_idenablenovalidate;

Tablealtered.

zx@ORA11G>select*fromt1;

IDNAMEADDRESS

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

1zxhb

2wlsd

1zqjx

zx@ORA11G>selecttable_name,constraint_name,constraint_type,deferrable,status,validatedfromuser_constraintswheretable_name='T1';

TABLE_NAMECONSTRAINT_NAMECDEFERRABLESTATUSVALIDATED

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

T1T1_UKUNOTDEFERRABLEENABLEDNOTVALIDATED

原表中的id列中有重复值,还是可以创建unique约束,因为状态指定为NOVALIDATE,不验证表中已有的数据。另外因为状态为ENABLE,再次插入重复值报错:

zx@ORA11G>insertintot1values(2,'yc','bj');

insertintot1values(2,'yc','bj')

*

ERRORatline1:

ORA-00001:uniqueconstraint(ZX.T1_UK)violated

3、测试DISABLE、VALIDATE状态

zx@ORA11G>select*fromt1;

IDNAMEADDRESS

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

1zxhb

2wlsd

zx@ORA11G>altertablet1addconstraintt1_ukunique(id)usingindexidx_t_iddisablevalidate;

Tablealtered.

zx@ORA11G>selecttable_name,constraint_name,constraint_type,deferrable,status,validatedfromuser_constraintswheretable_name='T1';

TABLE_NAMECONSTRAINT_NAMECDEFERRABLESTATUSVALIDATED

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

T1T1_UKUNOTDEFERRABLEDISABLEDVALIDATED

zx@ORA11G>insertintot1values(1,'zq','jx');

insertintot1values(1,'zq','jx')

*

ERRORatline1:

ORA-25128:Noinsert/update/deleteontablewithconstraint(ZX.T1_UK)disabledandvalidated

DISABLE、VALIDATE状态下,不允许做增删改操作。

4、测试DISABLE、NOVALIDATE状态

zx@ORA11G>select*fromt1;

IDNAMEADDRESS

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

1zxhb

2wlsd

1zqjx

zx@ORA11G>altertablet1addconstraintt1_ukunique(id)usingindexidx_t_iddisablenovalidate;

Tablealtered.

zx@ORA11G>selecttable_name,constraint_name,constraint_type,deferrable,status,validatedfromuser_constraintswheretable_name='T1';

TABLE_NAMECONSTRAINT_NAMECDEFERRABLESTATUSVALIDATED

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

T1T1_UKUNOTDEFERRABLEDISABLEDNOTVALIDATED

zx@ORA11G>insertintot1values(2,'yc','bj');

1rowcreated.

zx@ORA11G>commit;

Commitcomplete.

zx@ORA11G>select*fromt1;

IDNAMEADDRESS

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

1zxhb

2wlsd

1zqjx

2ycbj

约束状态为DISABLE、NOVALIDATE,对新数据和老数据都不做验证。

二、验证机制

1.两种验证时机.

Oracle的constraints(约束)根据验证时机可以分成两种.

case1.在每一句insertstatement执行时就会马上验证,如果约束验证失败,则这句sqlstatement会执行失败.

case2.执行insertstatements时不会验证,在commit的时候验证,如果验证失败,则整个Transaction回滚.

2.constraints的分类

对应地,oracle的constraints也可以分成两大类.

一种是notdeferrable(不可以延时的).这种情况下只能执行case1的验证时机(即时验证)

另一种是deferrable(可以设置成延时的).这种情况下可以执行case1或case2的验证时机.但需要设置.

对于第二种defferable分类,还可以分成两小类.

一种是initiallyimmediate,意思时默认情况下执行case1.

另一种是initiallydeferred,意思是默认情况下执行case2.

也就是可以分成三种,如下图:

2.1、notdeferrable

这种最常见也最简单.如果在增加1个constraint时不指定验证时机属性.默认情况下就会被设为notdeferrable.既然constraint是不可以延时验证的,所以也不用设定它的初始属性(实际上就是initiallyimmediate)。

清空上面的t1表,并创建一个unique约束

zx@ORA11G>truncatetablet1;

Tabletruncated.

zx@ORA11G>select*fromt1;

norowsselected

zx@ORA11G>altertablet1addconstraintt1_ukunique(id)notdeferrable;

Tablealtered.

zx@ORA11G>selecttable_name,constraint_name,constraint_type,deferrable,status,validatedfromuser_constraintswheretable_name='T1';

TABLE_NAMECONSTRAINT_NAMECDEFERRABLESTATUSVALIDATED

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

T1T1_UKUNOTDEFERRABLEENABLEDVALIDATED

约束为NOTDEFERRABLE状态,插入测试数据查看状态:

zx@ORA11G>insertintot1values(1,'zx','hb');

1rowcreated.

zx@ORA11G>insertintot1values(2,'wl','sd');

1rowcreated.

zx@ORA11G>insertintot1values(1,'zq','jx');

insertintot1values(1,'zq','jx')

*

ERRORatline1:

ORA-00001:uniqueconstraint(ZX.T1_UK)violated

zx@ORA11G>select*fromt1;

IDNAMEADDRESS

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

1zxhb

2wlsd

插入第三条数据时因为有重复数据,直接报错,说明验证时机为case1:即时验证,但不会回滚之前插入的结果。

2.2、deferrable、initiallyimmediate状态

zx@ORA11G>altertablet1dropconstraintt1_uk;

Tablealtered.

zx@ORA11G>altertablet1addconstraintt1_ukunique(id)deferrableinitiallyimmediate;

Tablealtered.

zx@ORA11G>selecttable_name,constraint_name,constraint_type,deferrable,status,validatedfromuser_constraintswheretable_name='T1';

TABLE_NAMECONSTRAINT_NAMECDEFERRABLESTATUSVALIDATED

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

T1T1_UKUDEFERRABLEENABLEDVALIDATED

zx@ORA11G>insertintot1values(1,'zx','hb');

1rowcreated.

zx@ORA11G>insertintot1values(2,'wl','sd');

1rowcreated.

zx@ORA11G>insertintot1values(1,'zq','jx');

insertintot1values(1,'zq','jx')

*

ERRORatline1:

ORA-00001:uniqueconstraint(ZX.T1_UK)violated

zx@ORA11G>select*fromt1;

IDNAMEADDRESS

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

1zxhb

2wlsd

插入第三条数据时报错因为有重复值,说明验证时机为case1:即时验证,这与前一种状态一样。那为什么还要设置这样一种状态呢?我们来执行下面的语句:

zx@ORA11G>setconstraintt1_ukdeferred;

Constraintset.

上面的语句并没有改变这个constraint的任何属性,只不过是切换为另一种模式

也就是说初始是immediate模式的,执行上面的语句后就临时变成deferred模式了.

再次执行前面的插入语句:

zx@ORA11G>insertintot1values(1,'zx','hb');

1rowcreated.

zx@ORA11G>insertintot1values(2,'wl','sd');

1rowcreated.

zx@ORA11G>insertintot1values(1,'zq','jx');

1rowcreated.

第三条也能插入进去,下面尝试commit:

zx@ORA11G>commit;

commit

*

ERRORatline1:

ORA-02091:transactionrolledback

ORA-00001:uniqueconstraint(ZX.T1_UK)violated

zx@ORA11G>select*fromt1;

norowsselected

commit时报错,查询t1表,没有任何数据,说明回滚了整个事务。即case2:延迟验证。此时再次执行上面的三次插入操作:

zx@ORA11G>insertintot1values(1,'zx','hb');

1rowcreated.

zx@ORA11G>insertintot1values(2,'wl','sd');

1rowcreated.

zx@ORA11G>insertintot1values(1,'zq','jx');

insertintot1values(1,'zq','jx')

*

ERRORatline1:

ORA-00001:uniqueconstraint(ZX.T1_UK)violated

从上面结果可以看出,插入第三行时又报错,说明上面的setconstraint语句的作用范围只有当前的一个事务。事务结束后即约束状态即回到原模式。

2.3、deferrable、initiallydeferred

有了上面的第二个实验就可以很容易的理解这一状态了。

zx@ORA11G>altertablet1dropconstraintt1_uk;

Tablealtered.

zx@ORA11G>altertablet1addconstraintt1_ukunique(id)deferrableinitiallydeferred;

Tablealtered.

zx@ORA11G>selecttable_name,constraint_name,constraint_type,deferrable,status,validatedfromuser_constraintswheretable_name='T1';

TABLE_NAMECONSTRAINT_NAMECDEFERRABLESTATUSVALIDATED

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

T1T1_UKUDEFERRABLEENABLEDVALIDATED

zx@ORA11G>insertintot1values(1,'zx','hb');

1rowcreated.

zx@ORA11G>insertintot1values(2,'wl','sd');

1rowcreated.

zx@ORA11G>insertintot1values(1,'zq','jx');

1rowcreated.

zx@ORA11G>commit;

commit

*

ERRORatline1:

ORA-02091:transactionrolledback

ORA-00001:uniqueconstraint(ZX.T1_UK)violated

zx@ORA11G>select*fromt1;

norowsselected