为什么 Oracle 不允许对 null 值引用列使用 DML？-解网

问：

当外键 R 约束引用的键的使用索引不可用时，为什么不允许 DML 用于空值引用列？

下面包含设置、一个插入示例、两个更新示例和两个删除示例。所有这些都不需要引用的索引，那么为什么它不可用很重要呢？重申，为什么 Oracle 甚至试图在索引中查找 null 值（因为索引中没有 null 值）？

设置

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 值不在唯一索引中，那么为什么不允许使用以下 DML？

为什么不允许在引用列中插入 null 值？

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

为什么不允许将引用列更新为 null？

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

为什么不允许删除包含 null 引用值的行？

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

启用外键 R 约束（DDL）不需要索引可用，那么，当 DML 涉及甚至不在索引中的值时，为什么 DML 要求索引有效才能强制实施 R 约束呢？

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.

使用 Oracle Database 19c 企业版 19.18.0.0.0。

oracle null 外键 unique-constraint unique-index

NULL 值存储在涉及多个列的索引中（除非索引中的每一列都是 NULL）。因此，Oracle 不能仅仅因为您将 NULL 插入到 FK 列中，就假定它不需要检查父表。虽然主键（P）不允许具有 NULL 列，但唯一键（U）允许 NULL 列，FK 可以指向唯一键和主键。

示范：

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)不在父表中 - 此插入应失败。这要求父项上的约束索引有效。

因此，如果要插入子行，Oracle 的内部代码会坚持在父行上使用有效的约束索引。我想如果他们检测它来检查约束类型（P 与 U）或列计数和索引类型，他们可能会找出不需要检查的情况，但他们没有这样做，而且约束索引处于不可用状态确实是异常的，所以这可能不是他们有动力做的事情。

至于删除，子行上的 DML 需要锁定父行，如果没有有效的约束索引，就无法查找要锁定的行。

0赞 MT0 4/3/2023 #2

当外键 R 约束引用的键的使用索引不可用时，为什么不允许 DML 用于空值引用列？

从约束文档中：

外键约束

外键约束（也称为引用完整性约束）将列指定为外键，并在该外键与指定的主键或唯一键（称为引用键）之间建立关系。

约束位于列和引用键之间，引用键由主键约束或唯一键约束支持;反过来，它由一个唯一的索引支持。

您已将索引设置为不可用：

alter index pk_t_parent unusable

这使得引用它的外键不可用。

是的，从理论上讲，Oracle 会检查引用的键是否不是复合键，如果是，则在外键设置为值时不检查索引;Oracle 不会这样做，你必须问 Oracle 为什么，因为他们的设计决策似乎没有公开记录。NULL

为什么不允许在引用列中插入 null 值？

因为您使外键引用的索引不可用。重新生成索引，您可以插入以下行：

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

为什么不允许将引用列更新为 null？

因为您使外键引用的索引不可用。重新生成索引，您可以更新该行：

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;

为什么不允许删除包含 null 引用值的行？

因为您使外键引用的索引不可用。重新生成索引，您可以删除该行：

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

小提琴

上一个：Oracle SQL 从列返回特定值，AND 任何空值 [closed]

下一个：比较 Oracle 中具有某些 null 值的两个表之间的数据

为什么 Oracle 不允许对 null 值引用列使用 DML？

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

评论

外键约束