oracle外键允许为空吗,sql – Oracle – 部分可以为空的复合外键

感谢所有的答案和评论.这个问题我迫使我学习新东西,这是件好事. @philipxy给了我很大的线索.我想回顾一下我学到的东西,因为它可能对其他人有用,而且这是一个记录它的好地方.

这个问题有两个方面：第一,部分无效的外键意味着什么,第二,它是如何实现的.

部分空外键的含义

关于这是什么意思存在很多争论 – 正如@ agiles231指出的那样. NULL可能意味着：

>价值未知.

>其他人说这意味着价值无效.

>其他人说NULL本身就是一个真正的价值.

简而言之,目前还没有明确的答案.

我想根据人们如何解释空值,然后在外键中使用它们(并验证它们)的策略可能会有所不同.

部分空外键的实现

SQL-92 Standard定义了(第4.10.2节)三种不同的方法来匹配具有可空值的复合外键：

>匹配SIMPLE：如果复合外键的任何列为空,则接受,存储外键,但不对引用的表进行验证.这通常是默认模式数据库提供的.在SQL-92标准中,描述了此模式但未命名.

> Match PARTIAL：如果复合外键的任何列为null,则每个非空列与引用的表匹配,以检查是否存在至少存在该值的行.我见过没有数据库实现这种模式.

>匹配已满：不接受部分为空的外键.外键完全为空或完全不为空.如果为null,则不会对引用的表进行验证.如果不为null,则对引用的表进行完全验证.这就是我所期待的默认行为(在我的幸福无知中).

好吧,我检查了10个不同的数据库如何实现这些模式,这是我发现的：

Database Engine Match SIMPLE Match PARTIAL Match FULL

--------------- ------------ ------------- ----------

Oracle 12c1 YES*1 NO NO

DB2 10.5 YES*1 NO NO

PostgreSQL 10 YES*1 NO YES

SQL Server 2014 YES*1 NO NO

MariaDB 10.3 YES*1 NO*2 NO*2

MySQL 8.0 YES*1 NO*2 NO*2

Sybase ASE 16 YES*1 NO YES

H2 1.4 YES*1 NO NO

Derby 10.13 YES*1 NO NO

HyperSQL 2.3 YES*1 NO YES

* 1这是默认模式.

* 2创建表时接受,但忽略.

简而言之：

>默认情况下,所有测试数据库的行为方式相同：默认为Match SIMPLE.

>我测试的数据库没有支持Match PARTIAL.我想这是有道理的,因为我个人觉得它没什么用处.此外,如果不在引用的表上创建所有可能的索引组合,在单独的外键列上执行部分验证可能会变得非常昂贵.

> PostgreSQL实现Match FULL以及Sybase ASE.这真是个好消息！令人惊讶的是,HyperSQL(这个小型数据库)也是如此.

实现Match FULL的解决方法

好消息是,在任何测试数据库中,如果您碰巧需要它,那么实现Match FULL是一个相当简单的解决方法.只需添加一个表约束,该约束允许所有空列或全部非空.就像是：

create table farm (

id int,

region_id int,

area_id int,

name varchar(50),

constraint fk_region_area foreign key (region_id, area_id)

references quadrant (region, area),

constraint fkfull_region_area check ( -- here's the workaround

region_id is null and area_id is null or

region_id is not null and area_id is not null)

);

insert into farm (id, region_id, area_id, name) values (5, 10, null, 'farm 1'); -- fails

insert into farm (id, region_id, area_id, name) values (6, 11, null, 'farm 2'); -- fails

insert into farm (id, region_id, area_id, name) values (7, 10, 125, 'farm 3'); -- succeeds

insert into farm (id, region_id, area_id, name) values (8, null, null, 'farm 4'); -- succeeds

它的工作非常整洁.

最后,作为一个非常个人的意见,我希望Match FULL成为默认的匹配策略.也许只是因为我允许(默认情况下)不指向其他行的外键会在使用数据库的应用程序中引发错误.

我认为与SIMPLE相比,大多数开发人员都会很容易理解. PARTIAL更复杂,并且可能容易出错.只是我的观点.