为什么这种外键可能？

Why does the SQL Standard accept this? Which are the benefits?

为什么SQL Standard接受这个?哪些好处?

If have those tables:

如果有那些表:

create table prova_a (a number, b number);
alter table prova_a add primary key (a,b);
create table prova_b (a number, b number);
alter table prova_b add foreign key (a,b) references prova_a(a,b) ;
insert into prova_a  values (1,2);

You can insert this without error:

你可以插入它而不会出错:

insert into prova_b  values (123,null);
insert into prova_b  values (null,123);

Note1: This comes from this answer.

注1:这来自这个答案。

Note2: This can be avoid, setting not null on both columns.

注意2:这可以避免,在两列上都不设置为null。

Remarks: I'm not asking about avoid, I'm interested on which are the beneficts.

备注:我不是要求避免,我对哪些是受益者感兴趣。

References:

Oracle documentation: The relational model permits the value of foreign keys to match either the referenced primary or unique key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.

Oracle文档:关系模型允许外键的值匹配引用的主键或唯一键值,或者为null。如果复合外键的任何列为空,则键的非空部分不必匹配父键的任何对应部分。
SQL Server documentation: A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped.

SQL Server文档:FOREIGN KEY约束可以包含空值;但是,如果复合FOREIGN KEY约束的任何列包含空值,则会跳过对构成FOREIGN KEY约束的所有值的验证。

5 个解决方案

#1

I know some DBMSs simply don't enforce referential integrity when it comes to foreign keys with foreign key constraints. SQLite comes to mind. It's talked about here.

我知道当涉及具有外键约束的外键时,一些DBMS根本不强制引用完整性。想到了SQLite。这是在这里谈到的。

Other DBMSs are different, I know that MS SQL Server will complain if you attempt something like that.

其他DBMS是不同的,我知道如果你尝试类似的东西,MS SQL Server会抱怨。

SQLite has its uses but it is not meant to be used in high-concurrency situations. If you are seeing this behavior in a different DBMS, check their documentation to see if they did something similar. Most should be enforcing integrity however.

SQLite有其用途,但它并不适用于高并发情况。如果您在不同的DBMS中看到此行为,请检查其文档以查看它们是否执行了类似的操作。但大多数人应该强制执行诚信。

#2

at least do your DEV work with a reasonably standard RDBMS, even if you are doing your production system with something like SQLite (which is an excellent database- it runs in your Ipod touch!) It will flush out all these mistakes- like Lint really. If you run your code with SQL Server Express, which you can download for free, you'll get plenty of errors such as...

至少你的DEV使用一个合理标准的RDBMS,即使你正在使用像SQLite这样的生产系统(这是一个优秀的数据库 - 它在你的Ipod触摸中运行!)它将清除所有这些错误 - 如Lint真的。如果你使用SQL Server Express运行你的代码,你可以免费下载,你会得到很多错误,如...

Msg 8111, Level 16, State 1, Line 2
Cannot define PRIMARY KEY constraint on nullable column in table 'prova_a'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

#3

Oracle and SQL Server both allow NULL foreign keys, and it is easily understandable why this is necessary. Think of a tree, for instance, where every row has a parent key that references the primary key of the same table. There has to be a root node in the tree that does not have a parent, and the parent key will be null. A more tangible example: think of employees and managers. Some people in the company, and if it is only the CEO, will not have a manager. Were it not possible to set the manager id on the employee table to NULL, you would have to create a "No Manager" employee - something that is just wrong, because it has no real-life correspondence.

Oracle和SQL Server都允许使用NULL外键,并且很容易理解为什么这是必要的。例如,可以考虑一棵树,其中每一行都有一个引用同一个表的主键的父键。树中必须有一个没有父节点的根节点,父键将为空。一个更切实的例子:想想员工和经理。公司中的一些人,如果只是CEO,就不会有经理。如果不能将employee表上的manager ID设置为NULL,则必须创建一个“No Manager”员工 - 这是错误的,因为它没有真实的对应关系。

Now that we know this, it is obvious why your composite keys behave like they do. Logically, if part of the composite is NULL, the entire key is null. A string concatenation returns NULL if one of the pieces is NULL. There cannot be a match, and the constraint is not enforced in these cases.

现在我们知道这一点,很明显为什么你的复合键表现得像他们一样。逻辑上,如果复合的一部分为NULL,则整个键为空。如果其中一个片段为NULL,则字符串连接返回NULL。不能匹配,并且在这些情况下不会强制执行约束。

#4

The SQL standard doesn't accept this; you've found a DBMS that doesn't enforce referential integrity. Uninstall it now if you're smart. At a bare minimum, don't use it for production purposes.

SQL标准不接受这个;您找到了一个不强制引用完整性的DBMS。如果你很聪明,现在就卸载它。至少,不要将其用于生产目的。

Earlier SQL standards (SQL86) had no referential integrity and SQL89 level 2 fixed that.

早期的SQL标准(SQL86)没有引用完整性,SQL89级别2修复了这一点。

#5

Try adding this declaration:

尝试添加此声明:

alter table prova_b add primary key (a,b);

alter table prova_b add primary key(a,b);

This will forbid NULLS in prova_b. It will also forbid duplicate entries. In Oracle and SQL server, it will also create an index. This index will speed up lookups and joins, at the cost of slowing down inserts a tiny bit.

这将禁止NULLS进行prova_b。它还将禁止重复输入。在Oracle和SQL服务器中,它还将创建索引。该索引将加速查找和连接,但代价是减慢插入量。

Is this what you want to do?

这是你想要做的吗?

As to why standard SQL lets you do something you consider stupid, that's a philosophical question. Most tools allow some stupid choices. Tools that try to forbid all stupid choices generally end up forbidding some really smart choices unintentionally.

至于为什么标准SQL允许你做一些你认为愚蠢的事情,这是一个哲学问题。大多数工具允许一些愚蠢的选择。试图禁止所有愚蠢选择的工具通常最终会无意中禁止一些非常明智的选择。

#1