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

jgovgodb 于 2023-04-05 发布在 Oracle

关注(0)|答案(2)|浏览(152)

为什么当外键R约束引用的键的using索引不可用时，DML不允许空值引用列？
下面包含了设置，一个插入示例，两个更新示例和两个删除示例。所有这些都不需要引用的索引，那么为什么它不可用呢？重申一下，为什么Oracle甚至试图在索引中查找空值（因为空值不在索引中）？
设置

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.

唯一索引中没有空值，那么为什么不允许使用以下DML？
为什么不允许在引用列中插入空值？

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

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

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要求索引是有效的，以强制执行R约束，当DML是关于甚至不在索引中的值时？

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 Enterprise Edition 19.18.0.0.0版。

oracle

来源：https://stackoverflow.com/questions/75913326/why-does-oracle-not-allow-dml-for-null-valued-referencing-columns

2条答案

按热度按时间

aelbi1ox1#

NULL值 * 存储在涉及多个列的索引中（除非索引中的每一列都是NULL）。因此，Oracle不能假设仅仅因为您将NULL插入FK列，它就不需要检查父表。（P）不允许有NULL列，unique keys（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 vs. U）或列计数和索引类型，它们可能会找出不需要检查的情况，但它们没有这样做，并且约束索引处于不可用状态确实是不正常的，所以这可能不是他们有动力去做的事情。
对于删除，子行上的DML需要锁定父行，如果没有有效的约束索引，则无法查找要锁定的行。

赞(0）回复(0）举报 2023-04-05

gg58donl2#

为什么当外键R约束引用的键的using索引不可用时，DML不允许空值引用列？
来自约束文档：

外键约束

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

该约束位于列和引用的键之间，该键由主键约束或唯一键约束支持;该索引又由唯一索引支持。
您已将索引设置为不可用：

alter index pk_t_parent unusable

这使得引用它的外键不可用。
是的，理论上，Oracle检查引用的键是否不是复合键，如果是，当外键设置为NULL值时，不检查索引;Oracle没有这样做，你必须问Oracle为什么，因为他们的设计决策似乎没有公开记录。
为什么不允许在引用列中插入空值？
因为您使外键引用的索引不可用。请重新生成索引，然后可以插入行：

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;

为什么不允许删除包含空引用值的行？
因为您使外键引用的索引不可用。请重新生成索引，然后可以删除该行：

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

fiddle

赞(0）回复(0）举报 2023-04-05

我来回答

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

2条答案

外键约束

相关问题

热门标签

最新问答