Four pairs of attributes of the Constraint in Oracle Database

We will create someConstraintConstraints, including primary keys and Foreign keys. Does it have attributes? The answer is yes. In this article, we will introduceOracleFour attributes of database Constraint constraints: Deferrable/not deferrable, Deferred/immediate, enalbe/disable, validate/novalidate. Let's take a look at this process.

1. Deferrable, not deferrable (default value)

1) This defines whether the attribute can be defer. defer refers to the time for checking. If the check is Defer at commit, otherwise it is immediate. you can set another property 2 -- Deferred, immediate.

2) There are two ways to set defer check (the premise is that the contraint of Deferrable is created)

A. Specify the Deferred value when creating a contraint

B. Modify statements at the session level

Set constraint (s) contraint_name/all immediate/deferred.

3) This attribute is defined when the constraint is created and cannot be modified.

4) notice: If a Deferrable uk or pk is created, only the corresponding nonuniquce index is created, and the uniquce index is not created.

2. Deferred, immediate (default value)

1) Whether defer. Deferred: check on commit; immediate: check immediate.

2) If constraint is not deferrable, immediate is only choice.

3) For example:

 
 

  
  
Create table games
  
  
 
  
  
(Scores NUMBER, CONSTRAINT unq_num UNIQUE (scores)
  
  
 
  
  
Initially deferred deferrable );
  
  
 
  
  
Insert into games values (1 );
  
  
 
  
  
Insert into games values (1 );
  
  
 
  
  
Commit; -- an error is reported here
  
  
 
  
  
You will not get a error util you commit it;
  
  
 
  
  
Set constraint (s) unq_num immediate; -- modify attributes
  
  
 
  
  
Insert into games values (2 );
  
  
 
  
  
Insert into games values (2); -- an error is reported here.
  
  
 
  
  
Commit;
  
  
 
  
  
You will get a error when you execute the second SQL;
 
 

3. novalidate, validate (default value)

1) This defines whether the attribute constraint checks existing data in the table, for example:

 
 

  
  
Create table t (id number );
  
  
 
  
  
Insert into t values (1 );
  
  
 
  
  
Insert into t values (2 );
  
  
 
  
  
Alter table t add constraint ch_100 check (id> = 100); -- failed
  
  
 
  
  
Alter table t add constraint ch_100 check (id> = 100) novalidate; -- successful
 
 

2) notice: The contraint (such as pk and uk) related to the unique index must be set to Deferrable (only a non-unique index is created) to achieve the above effect ), if the uniqueness is violated, an error is returned. Therefore, a non-uniqueness index must be created. for example:

 
 

  
  
Drop table t;
  
  
 
  
  
Create table t (id number );
  
  
 
  
  
Insert into t values (1 );
  
  
 
  
  
Insert into t values (1 );
  
  
 
  
  
Alter table t add constraint ch_100 unique (id); -- Error
  
  
 
  
  
Alter table t add constraint ch_100 unique (id) novalidate; -- Error
  
  
 
  
  
Alter table t add constraint ch_100 unique (id) deferrable novalidate; -- successful
 
 

4. disable, enalbe (default value)

1) Enable and disable constraint. disable is defined when pk and uk are created, and no corresponding index is created.

 
 

  
  
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;  
  
  
 
  
  
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;  
  
  
 
  
  
alter table t add constraint ch_100 unique(id) disable; 
 
 

2) What does DISABLE uk or pk do:

Disable non-deferrable pk and uk will delete the corresponding index (unless the keep index is specified, but the index after the keep is unique, and the uniqueness check should be performed during data insert ), re-indexing during enable.

The primary key and uk of Disbale deferrable will retain the original index (because the original index is non-unique and does not affect the insert operation ).

3) Some operation experience:

Note the following for keep index:

A. alter table games DISAble CONSTRAINT fk_num keep index; -- the unique index is retained, So duplicate data cannot be inserted.

B. alter table games DISAble CONSTRAINT fk_num; -- if the statement is executed in the previous step, the statement will not do anything, and the unique index will still be retained. In this case, enable in the disable. if the original status is able, the unique index will be deleted.

Here is an introduction to the four attributes of the Oracle database constraint constraints. I hope this introduction will be helpful to you!