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