Why does Oracle not allow DML for null valued referencing columns?

Question

Why is DML not allowed for null valued referencing columns when the using index of the key referenced by the foreign key R constraint is unusable?

Below contains the setup, an insert example, two update examples, and two delete examples. All of which do not require the referenced index, so why does it matter that it is unusable? Restated, why is Oracle even trying to look up null values in the index (as null values are not in the index)?

Setup

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 2 11:28:13 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Connected.
 
SQL>set sqlterminator off
SQL>create table t_parent
  2             ( id_parent  number constraint pk_t_parent primary key
  3             , val        varchar2(5)
  4             )
  5  /
 
Table created.
 
SQL>create table t_child
  2             ( id_child  number
  3             , id_parent number
  4             , val       varchar2(5)
  5             , constraint fk_t_child_t_parent foreign key (id_parent) references t_parent (id_parent)
  6             )
  7  /
 
Table created.
 
SQL>insert into t_parent( id_parent, val) values( 1, 'A')
  2  /
 
1 row created.
 
SQL>insert into t_child( id_child, id_parent, val) values( 1, 1, 'A')
  2  /
 
1 row created.
 
SQL>insert into t_child( id_child, id_parent, val) values( 2, null, 'B')
  2  /
 
1 row created.
 
SQL>alter index pk_t_parent unusable
  2  /
 
Index altered.

Null values are not in the unique index, so why are the following DMLs not allowed?

Why is inserting a null value into the referencing column not allowed?

SQL>insert into t_child( id_child, id_parent, val) values( 4, null, 'D')
  2  /
insert into t_child( id_child, id_parent, val) values( 4, null, 'D')
*
ERROR at line 1:
ORA-01502: index 'PK_T_PARENT' or partition of such index is in unusable state

Why is updating the referencing column to null not allowed?

SQL>update t_child set id_parent = null where id_parent = 1
  2  /
update t_child set id_parent = null where id_parent = 1
*
ERROR at line 1:
ORA-01502: index 'PK_T_PARENT' or partition of such index is in unusable state
 
SQL>update t_child set id_parent = null where id_parent is null
  2  /
update t_child set id_parent = null where id_parent is null
*
ERROR at line 1:
ORA-01502: index 'PK_T_PARENT' or partition of such index is in unusable state

Why is deleting a row that contains a null referencing value not allowed?

SQL>delete from t_child where id_parent is null
  2  /
delete from t_child where id_parent is null
*
ERROR at line 1:
ORA-01502: index 'PK_T_PARENT' or partition of such index is in unusable state

To enable the foreign key R constraint (DDL) does not require the index to be usable, so why does DML require the index to be valid to enforce the R constraint when that DML is regarding values that are not even in the index?

SQL>alter table t_child modify constraint fk_t_child_t_parent disable
  2  /
 
Table altered.
 
SQL>alter table t_child modify constraint fk_t_child_t_parent enable
  2  /
 
Table altered.

Using Oracle Database 19c Enterprise Edition Release 19.18.0.0.0.

Paul W · Accepted Answer · 2023-04-02T19:08:57.633

NULL values are stored in indexes that involve multiple columns (unless every column in the index is NULL). For this reason, Oracle cannot assume that just because you are inserting a NULL into an FK column it need not check the parent table. While primary keys (P) are not allowed to have NULLable columns, unique keys (U) do allow NULL columns and FKs can point to unique keys as well as primary keys.

Demonstration:

create table test$parent (parent_uk1 integer null,parent_uk2 integer not null,
constraint uk_test$parent unique (parent_uk1,parent_uk2));
create table test$child (child_pk integer,fk_to_parent1 integer null,fk_to_parent2 not null, 
constraint pk_test$child primary key (child_pk),
constraint fk_test$child_parent foreign key (fk_to_parent1,fk_to_parent2) references test$parent(parent_uk1,parent_uk2))
        

insert into test$parent values (1,1);
insert into test$parent values (null,1);

insert into test$child values (100,1,1);
insert into test$child values (200,null,1);

commit;

alter index uk_test$parent unusable;

insert into test$child values (300,null,3);

*raises ORA-01502*

(null,3) is not in the parent table - this insert SHOULD fail. That requires that the constraint index on the parent be valid.

As a result, Oracle's internal code insists on a valid constraint index on the parent if you want to insert a child row. I suppose if they instrumented it to check the constraint type (P vs. U) or column count and index-type they could possibly figure out situations where the check is unneeded, but they haven't done so, and it really is abnormal for a constraint index to be in an unusable state, so it's probably not something they are motivated to do.

As for the delete, a DML on a child row requires locking the parent row, and the lookup of what row to lock cannot be done without a valid constraint index.

score 0 · Answer 2 · answered Apr 02 '23 at 20:39

Why is DML not allowed for null valued referencing columns when the using index of the key referenced by the foreign key R constraint is unusable?

From the constraint documentation:

Foreign Key Constraints

A foreign key constraint (also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key.

The constraint is between the column and a referenced key which is backed by either a primary key constraint or a unique key constraint; which, in turn, is backed by a unique index.

You have set the index to be unusable:

alter index pk_t_parent unusable

Which makes the foreign key referencing it unusable.

Yes, theoretically, Oracle check to see if the referenced key is not a composite key and, if so, not check the index when the foreign key is set to a NULL value; Oracle doesn't do that and you would have to ask Oracle why as their design decisions does not appear to be publicly documented.

Why is inserting a null value into the referencing column not allowed?

Because you made the index that the foreign key references unusable. Rebuild the index and you can insert the row:

alter index pk_t_parent REBUILD;
insert into t_child( id_child, id_parent, val) values( 4, null, 'D');

Why is updating the referencing column to null not allowed?

Because you made the index that the foreign key references unusable. Rebuild the index and you can update the row:

alter index pk_t_parent REBUILD;
update t_child set id_parent = null where id_parent = 1;
update t_child set id_parent = null where id_parent is null;

Why is deleting a row that contains a null referencing value not allowed?

Because you made the index that the foreign key references unusable. Rebuild the index and you can delete the row:

alter index pk_t_parent REBUILD;
delete from t_child where id_parent is null;

fiddle

Why does Oracle not allow DML for null valued referencing columns?

2 Answers2

Foreign Key Constraints