oracle约束数量,oracle 约束(constraint)的几个参数的小研究

--deferred 跟 immediate的对比试验-----------------------------

SQL> drop table aa purge;

Table dropped.

SQL> create table aa ( id number,name varchar2(20),constraint pk primary key(id));

Table created.

SQL> col constraint_name for a11

SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME,STATUS,DEFERRABLE,DEFERRED,validated from u

ser_constraints where table_name='AA';

CONSTRAINT_ C TABLE STATUS   DEFERRABLE     DEFERRED  VALIDATED

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

PK          P AA    ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

--可以看到，默认的是NOT DEFERRABLE，下面我们将它转为immediate试试

SQL> set constraint pk immediate;

set constraint pk immediate

*

ERROR at line 1:

ORA-02447: cannot defer a constraint that is not deferrable

--增加一个uk，指定deferrable initially immidiate|deferred

SQL> alter table aa add constraint uk unique (name) deferrable initially immediate;

Table altered.

SQL> select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME,STATUS,DEFERRABLE,DEFERRED,validated from u

ser_constraints where table_name='AA';

CONSTRAINT_ C TABLE STATUS   DEFERRABLE     DEFERRED  VALIDATED

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

PK          P AA    ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

UK          U AA    ENABLED  DEFERRABLE     IMMEDIATE VALIDATED

SQL> select * from aa;

no rows selected

SQL> insert into aa values(1,'SDF');

1 row created.

SQL> insert into aa values(2,'SDF');

insert into aa values(2,'SDF')

*

ERROR at line 1:

ORA-00001: unique constraint (LYN.UK) violated

SQL> set constraints uk deferred;

Constraint set.

SQL> select * from aa;

no rows selected

SQL> insert into aa values(1,'SDF');

1 row created.

SQL> insert into aa values(2,'SDF');

1 row created.

SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (LYN.UK) violated

--此外，可以在SESSION里设定所有约束是立即检查(immediate)还是延迟检查(deferred)，当然，这只影响初始时指定可延迟的(deferrable)约束。

SQL> alter session set constraint=immediate;

Session altered.

SQL> alter session set constraints=deferred;

Session altered.

--4种状态对比-----------------

SQL> drop table aa purge;

Table dropped.

SQL> create table aa(id number primary key);

Table created.

--enable validate(默认)

SQL>  select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME,STATUS,DEFERRABLE,DEFERRED,validated from

user_constraints where table_name='AA';

CONSTRAINT_ C TABLE STATUS   DEFERRABLE     DEFERRED  VALIDATED

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

SYS_C002829 P AA    ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

SQL> insert into aa select 2010 from dual;

1 row created.

SQL> insert into aa select 2010 from dual;

insert into aa select 2010 from dual

*

ERROR at line 1:

ORA-00001: unique constraint (LYN.SYS_C002829) violated

--disable novalidate

SQL> alter table aa modify constraints SYS_C002829 disable;

Table altered.

SQL>  select CONSTRAINT_NAME ,CONSTRAINT_TYPE,TABLE_NAME,STATUS,DEFERRABLE,DEFERRED,validated from

user_constraints where table_name='AA';

CONSTRAINT_ C TABLE STATUS   DEFERRABLE     DEFERRED  VALIDATED

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

SYS_C002829 P AA    DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

SQL> select * from aa;

ID

----------

2010

SQL> insert into aa select 2010 from dual;

1 row created.

SQL> insert into aa select 2010 from dual;

1 row created.

SQL> commit;

Commit complete.

--disable validate

SQL> alter table aa modify constraints SYS_C002829 disable validate;

alter table aa modify constraints SYS_C002829 disable validate

*

ERROR at line 1:

ORA-02437: cannot validate (LYN.SYS_C002829) - primary key violated

SQL> select * from aa;

ID

----------

2010

2010

2010

SQL> delete aa where rownum<3;

2 rows deleted.

SQL> select rownum,id from aa;

ROWNUM         ID

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

1       2010

SQL> alter table aa modify constraints SYS_C002829 disable validate;

Table altered.

SQL> update aa set id=2012;

update aa set id=2012

*

ERROR at line 1:

ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabledand validated

SQL> insert into aa select 2012 from dual;

insert into aa select 2012 from dual

*

ERROR at line 1:

ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabledand validated

SQL> delete from aa;

delete from aa

*

ERROR at line 1:

ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabledand validated

SQL> truncate table aa;

truncate table aa

*

ERROR at line 1:

ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829) disabledand validated

--enable novalidate

SQL> alter table aa enable novalidate constraint SYS_C002829;

Table altered.

SQL> insert into aa select 2010 from dual;

insert into aa select 2010 from dual

*

ERROR at line 1:

ORA-00001: unique constraint (LYN.SYS_C002829) violated