首页 » ORACLE » Specifying Constraint State

Specifying Constraint State

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

打赏

目前这篇文章有3条评论(Rss)评论关闭。

  1. Gladys Denbo | #1
    2011-12-21 at 14:12

    Spot on with this write-up, I truly think this website needs much more consideration. I’ll probably be again to read much more, thanks for that info.

  2. Gilbert Leriche | #2
    2011-11-15 at 23:52

    This website is certainly fairly useful because I’m on the instant producing a web floral internet site – though I’m only beginning out as a result it is truly rather tiny, practically nothing similar to this internet site. Can website link to some from the posts right here because they are very. Many thanks considerably. Zoey Olsen

  3. iphone 5 | #3
    2011-10-22 at 12:58

    I’d must look at with you right here. Which can be not something I generally do! I just take satisfaction in reading through a submit which will make people feel. In addition, many thanks for permitting me to comment!