感谢所有的答案和评论.这个问题我迫使我学习新东西,这是件好事. @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更复杂,并且可能容易出错.只是我的观点.