ANBOB™

Specifying Constraint State

As part of constraint definition, you can specify how and when Oracle should enforce the constraint.

constraint_state
You can use the constraint_state with both inline and out-of-line specification. You can specify the clauses of constraint_state in any order, but you can specify each clause only once.

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.

INITIALLY Clause
The INITIALLY clause establishes the default checking behavior for constraints that are DEFERRABLE. The INITIALLY setting can be overridden by a SET CONSTRAINT(S) statement in a subsequent transaction.This clause is not valid if you have declared the constraint to be NOT DEFERRABLE, because a NOT DEFERRABLE constraint is automatically INITIALLY IMMEDIATE and cannot ever be INITIALLY DEFERRED.

anbob@ANBOB> drop table testcons purge;

Table dropped.

anbob@ANBOB> create table testcons(id int,name varchar2(10),
2      constraint pk_id primary key (id) );

Table created.

anbob@ANBOB> insert into testcons values(1,’anbob’);

1 row created.

anbob@ANBOB> insert into testcons values(1,’anbob’);
insert into testcons values(1,’anbob’)
*
ERROR at line 1:
ORA-00001: unique constraint (ANBOB.PK_ID) violated

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> select * from testcons;

ID NAME
———- ——————————
1 anbob

anbob@ANBOB> delete testcons;

1 row deleted.

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> set constraint pk_id deferred;
set constraint pk_id deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable

anbob@ANBOB> alter table testcons drop constraint pk_id;

Table altered.

anbob@ANBOB> alter table testcons  add constraint pk_id primary key (id) deferrable initially deferred;

Table altered.

anbob@ANBOB> select count(*) from testcons;

COUNT(*)
———-
0

anbob@ANBOB> insert into testcons values(1,’anbob’);

1 row created.

anbob@ANBOB> insert into testcons values(1,’anbob’);

1 row created.

anbob@ANBOB> select * from testcons;

ID NAME
———- ——————————
1 anbob
1 anbob

anbob@ANBOB> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (ANBOB.PK_ID) violated

anbob@ANBOB> select * from testcons;

no rows selected

anbob@ANBOB> set constraint pk_id immediate;

Constraint set.

anbob@ANBOB> insert into testcons values(1,’anbob’);

1 row created.

anbob@ANBOB> insert into testcons values(1,’anbob’);
insert into testcons values(1,’anbob’)
*
ERROR at line 1:
ORA-00001: unique constraint (ANBOB.PK_ID) violated

anbob@ANBOB> select * from testcons;

ID NAME
———- ——————————
1 anbob

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> select * from testcons;

ID NAME
———- ——————————
1 anbob

anbob@ANBOB> set constraint pk_id deferred;

Constraint set.

anbob@ANBOB> insert into testcons values(1,’anbob’);

1 row created.

anbob@ANBOB> insert into testcons values(1,’anbob’);

1 row created.

anbob@ANBOB> insert into testcons values(1,’anbob’);

1 row created.

anbob@ANBOB> select * from testcons;

ID NAME
———- ——————————
1 anbob
1 anbob
1 anbob

anbob@ANBOB> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (ANBOB.PK_ID) violated

anbob@ANBOB> select * from testcons;

no rows selected

打赏

微信扫一扫，打赏作者吧～