Skip to Main Content


Question and Answer

Chris Saxon

Thanks for the question, Keith.

Asked: November 05, 2001 - 3:59 pm UTC

Last updated: November 16, 2021 - 4:50 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

I Recently implemented constraints on a system as follows:

i) All primary keys were not declared as being not deferrable.
ii) All foreign keys were declared as being deferrable initially deferred.

The reasoning behind having the primary keys as not being deferrable was due to uniqueness not being enforced until commit time, and I get the impression that lookups using the index on a deferrable Constraint will be slower than the lookups using the index on a normal constraint because uniqueness is not enforced on deferrable constraints.

The reasoning for having the foreign keys as being deferrable,initially deferred was to allow for easier programming in the application. There was no need to alter the constraints to be deferred or to worry about the order of inserts into the tables.

Is this a sensible guideline to follow for using deferred integrity constraints

and Tom said...

With primary keys -- if you set them to be deferrable but initiallly deferred what will happen is that they are validated right away but are enforced using a non-unique index (to allow for dups temporarily). You would use deferrable primary keys only when you needed the functionality (eg: when refreshing a snapshot -- the snapshot must have deferrable primary/unique constraints).

With foreign keys -- i think it is a totally bad idea to have them initially deferred. Many applications sort of assume a commit will succeed always. Also, in an application, entering data in the proper order should be trivial. It is nice to have the deferrable however to allow for an infrequent update cascade or to do a load in any order. I just think having them deferred by default is so out of the ordinary it would be asking for trouble.


  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment


FYI, a scenario of using deferrable constraint

Frank, November 06, 2001 - 11:00 am UTC

1)any department should has employee
2)any employee should be assigned to a department
so, employee table has a FK links to the department table, and another check constraint should be added to department table to ensure there should be some employee in this department. It's kind of dead lock idea, each one depends on the other one. You cant insert new department, coz there is no employee. On the other hand, u cant insert new employee belongs to this new department, coz the department hasnt been and cant be added. So the check constraint can be a deferrable constraint, which solves this delimma.

Any side effect of using 'deferrable' as default

A reader, August 03, 2005 - 4:46 pm UTC


I tested with 'DEFERRABLE' constraints to some extent and it looks the same as 'NON-DEFERRABLE' but it creates the NON-UNIQUE index (instead of unique) as primary key index.

Do you know of any problem if i make all my future new tables Primary Key as 'DEFERRABLE'. Hope not.

Tom Kyte
August 03, 2005 - 8:20 pm UTC

don't forget, a primary key is two things

a) unique
b) not null

the not null part could "get you", consider:

ops$tkyte@ORA10GR1> create table t
  2  ( x int constraint t_pk primary key deferrable,
  3    y int )
  4  /
Table created.
ops$tkyte@ORA10GR1> exec dbms_stats.set_table_stats( user, 'T', numrows =>1000000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select count(*) from t;
Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=59 Card=1000000)
ops$tkyte@ORA10GR1> alter table t modify x not null;
Table altered.
ops$tkyte@ORA10GR1> select count(*) from t;
Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T_PK' (INDEX) (Cost=9 Card=1000000)
ops$tkyte@ORA10GR1> set autotrace off

when x is not know to always be NOT NULL, the index may be "not used" in some circumstances.

I recently had a discussion with Jonathan Lewis on this topic -- conclusion was that you probably don't want to default to deferrable, use it when you want it. 

Thanks tom

A reader, August 04, 2005 - 12:14 am UTC


Thanks for sharing this.

I tested this and observed that 'deferrable' PK do not show up as 'not null' when describing the table. But if we insert 'null' for that, they raise "cannot insert null". This is cool as it shows they did not lose the PK property of having 'not null' in DB. 

I understand that in order for 'deferrable' constraints to work, they did not make it 'not null' in data dictionary. However, as you suggested one should explicitly define 'NOT NULL' constraint on deferrable PK columns (just for index access path). 

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER(38)
 Y                                                  NUMBER(38)

SQL> select table_name, constraint_name, constraint_type, deferrable from user_constraints where table_name='T'

TABLE_NAME                     CONSTRAINT_NAME                C DEFERRABLE
------------------------------ ------------------------------ - --------------
T                              T_PK                           P DEFERRABLE

SQL>select TABLE_NAME, COLUMN_NAME, NULLABLE from user_tab_columns where table_name='T' and column_name='X' ;

TABLE_NAME                     COLUMN_NAME                    N
------------------------------ ------------------------------ -
T                              X                              Y

Tom Kyte
August 04, 2005 - 9:13 am UTC

If you DEFER the constraint, they certain can hold nulls, unless and until you put the additional NOT NULL constraint that is not deferrable of course.

What about a pigs ear relationship?

Paul Hill, November 16, 2021 - 3:16 pm UTC

"i think it is a totally bad idea to have them initially deferred".

Sometimes there is no choice! A pigs ear relationship e.g. managers and employees, stored in the same table will usual cause problems with FK constraints unless they are deferred.

Unless you are able to insert data ensuring the manager of an employee is inserted before the employee, then the FK will fail resulting in an error. So, when this is not possible or is just unnecessary, the obvious thing to do is make the foreign key constraint deferrable and this problem goes away - employees and managers can be inserted in any order and are only validated on commit.

This is usually the requirement when doing bulk inserts into a table that has a foreign key relationship to itself.
Chris Saxon
November 16, 2021 - 4:50 pm UTC

It's a good point - there are a small number of cases where you need deferrable FKs to load the data. But these are rare so I'd only have deferrable FKs when you truly need it.