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
Tom,
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.
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>
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
Tom,
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
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.
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.