patternMinor

Deferrable Foreign Key Constraints

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

deferrableconstraintsforeignkey

Problem

What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE?

This answer on StackOverflow mentions the performance impact that comes with no longer using a unique index for a deferrable unique constraint and I can relate to that. However, what are disadvantages or side-effects of making a foreign key deferrable?

I can see only one disadvantage: inconsistencies introduced by earlier writes may go undetected until a COMMIT occurs. However, our application's storage layer delays all DML statements until the end of the transaction, so an inconsistent DB state is never read (not even by the code causing it) and only detected upon COMMIT anyway.

Are there others?

If it matters, we are thinking about making all foreign keys in a certain table family DEFERRABLE INITIALLY IMMEDIATE, but only make them deferred in one specific job (out of 20 or so that access these tables) that performs a large amount of mutually dependent inserts/updates/deletes.

TL;DR: What do we have to be aware of when making foreign keys DEFERRABLE INITIALLY DEFERRED?

Solution

Deferrable constraints can prevent optimizer transformations. Here's a simple example validating percentages must be between zero and one hundred:

create table exam_results (
  student_id integer, exam_id integer,
  percentage_correct number,
  primary key ( student_id, exam_id )
);

alter table exam_results
  add constraint exre_pct_correct_c
  check ( percentage_correct between 0 and 100 );

With a not deferrable constraint the optimizer knows if you search for values outside this range the query must return no rows. So if you do it can apply a transformation to bypass the table:

set serveroutput off

select * from exam_results
where  percentage_correct  'BASIC +PREDICATE' );

---------------------------------------------------
| Id  | Operation                  | Name         |
---------------------------------------------------
|   0 | SELECT STATEMENT           |              |
|*  1 |  FILTER                    |              |
|*  2 |   TABLE ACCESS STORAGE FULL| EXAM_RESULTS |
---------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)
   2 - storage("PERCENTAGE_CORRECT"<0)
       filter("PERCENTAGE_CORRECT"<0)

Notice the filter operation NULL IS NOT NULL is a parent of the full scan operation. Because it's false, there's no need to run the scan. The optimizer has stopped the query accessing the table at all!

If you re-create the constraint as deferrable, then you may temporarily have invalid data. This applies even if you declare it initially immediate because you can change the state to deferred within sessions/transactions.

So the optimizer can no longer rely on the constraint:

alter table exam_results
  drop constraint exre_pct_correct_c;
  
alter table exam_results
  add constraint exre_pct_correct_c
  check ( percentage_correct between 0 and 100 )
  deferrable initially immediate;

select * from exam_results
where  percentage_correct  'BASIC +PREDICATE' );

--------------------------------------------------
| Id  | Operation                 | Name         |
--------------------------------------------------
|   0 | SELECT STATEMENT          |              |
|*  1 |  TABLE ACCESS STORAGE FULL| EXAM_RESULTS |
--------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("PERCENTAGE_CORRECT"<0)
       filter("PERCENTAGE_CORRECT"<0)

The plan no longer has filter operation. The database reads every row in the table, checking to see if the percentage is below zero.

TL;DR Without the guarantees validated, not deferrable constraints bring, you limit the optimizer's ability to generate better plans.

Code Snippets

create table exam_results (
  student_id integer, exam_id integer,
  percentage_correct number,
  primary key ( student_id, exam_id )
);

alter table exam_results
  add constraint exre_pct_correct_c
  check ( percentage_correct between 0 and 100 );

set serveroutput off

select * from exam_results
where  percentage_correct < 0;

select * 
from   dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE' );

---------------------------------------------------
| Id  | Operation                  | Name         |
---------------------------------------------------
|   0 | SELECT STATEMENT           |              |
|*  1 |  FILTER                    |              |
|*  2 |   TABLE ACCESS STORAGE FULL| EXAM_RESULTS |
---------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)
   2 - storage("PERCENTAGE_CORRECT"<0)
       filter("PERCENTAGE_CORRECT"<0)

alter table exam_results
  drop constraint exre_pct_correct_c;
  
alter table exam_results
  add constraint exre_pct_correct_c
  check ( percentage_correct between 0 and 100 )
  deferrable initially immediate;

select * from exam_results
where  percentage_correct < 0;

select * 
from   dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE' );

--------------------------------------------------
| Id  | Operation                 | Name         |
--------------------------------------------------
|   0 | SELECT STATEMENT          |              |
|*  1 |  TABLE ACCESS STORAGE FULL| EXAM_RESULTS |
--------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("PERCENTAGE_CORRECT"<0)
       filter("PERCENTAGE_CORRECT"<0)

Context

StackExchange Database Administrators Q#102346, answer score: 2

Revisions (0)

No revisions yet.