为什么我可以在可空列上创建一个带PRIMARY KEY的表？ [英] Why can I create a table with PRIMARY KEY on a nullable column?

问题描述

以下代码创建了一个表格，但不会引发任何错误：

  CREATE TABLE test（
 ID INTEGER NULL， 
 CONSTRAINT PK_test PRIMARY KEY（ID）
）
  

请注意，一个NULL，如预期的：

  INSERT INTO test 
 VALUES（1），（NULL）
 ERROR ：id列中的空值违反非空约束
详细信息：失败的行包含（null）。 
 **********错误********** 
 
错误：列id中的空值违反了非null约束
 SQL状态：23502 
详细信息：Failing row contains（null）。 
  

为什么可以创建一个具有自相矛盾定义的表？ ID列被显式声明为NULLable，并且作为PRIMARY KEY的一部分，它是隐式不可为空的。是否有意义？

编辑：如果这个自相矛盾的CREATE TABLE刚刚失败了，会更好吗？

PRIMARY KEY 使列 NOT NULL 自动。我引用此处的说明：

主键约束指定
表的一列或多列只能包含唯一（非重复），非空值。
技术上， PRIMARY KEY 只是 UNIQUE 和 NOT NULL 。

大胆强调我。

我运行一个测试来确认（对我以前的信念！） NOT NULL 是完全冗余与 PRIMARY KEY 约束（在当前实现中，最高版本9.5）。删除PK约束后， NOT NULL约束保持，而不考虑创建时的显式 NOT NULL 子句。

  db =＃CREATE TEMP TABLE foo（foo_id int PRIMARY KEY）; 
注意：CREATE TABLE / PRIMARY KEY将为表foo创建隐式索引foo_pkey
 CREATE TABLE 
 
 db =＃ALTER TABLE foo DROP CONSTRAINT foo_pkey; 
 ALTER TABLE  

  
 db =＃\d foo 
 table»pg_temp_4.foo«
 column |类型|属性
 -------- + --------- + ----------- 
 foo_id |整数|  not null   

NULL 包含在 CREATE 语句中。

如果列应该是 NOT NULL ，则会在代码存储库中冗余地保留 NOT NULL 。如果你以后决定移动pk约束，你可能会忘记标记列 NOT NULL - 或者它是否应该是 NOT NULL 。

Postgres TODO wiki 从PK约束中解耦 NOT NULL 。因此，在将来的版本中，这可能会改变：

将NOT NULL约束信息移动到pg_constraint

当前NOT NULL约束存储在pg_attribute中，而没有指定它们的起点，例如主键。一个清单
的问题是，删除PRIMARY KEY约束不会删除
NOT NULL约束指定。另一个问题是，我们应该
可能强制NOT NULL从父表传播到
子对象，就像CHECK约束一样。 （但是，
PRIMARY KEY会影响儿童吗？）

回答添加的问题：

如果这个自相矛盾的CREATE TABLE只是
失败，那会更好吗？

如上所述，

  foo_id INTEGER NULL PRIMARY KEY 
  

等效于：

  foo_id INTEGER PRIMARY KEY 
  

由于 NULL 作为噪声词。

我们不希望后者失败。所以这不是一个选项。

The following code creates a table without raising any errors:

CREATE TABLE test(
ID INTEGER NULL,
CONSTRAINT PK_test PRIMARY KEY(ID)
)

Note that I cannot insert a NULL, as expected:

INSERT INTO test
VALUES(1),(NULL)
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null).
********** Error **********

ERROR: null value in column "id" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null).

Why can I create a table with a self-contradictory definition? ID column is explicitly declared as NULLable, and it is implicitly not nullable, as a part of the PRIMARY KEY. Does it make sense?

Edit: would it not be better if this self-contradictory CREATE TABLE just failed right there?

解决方案

Because the PRIMARY KEY makes the column NOT NULL automatically. I quote the manual here:

The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL.

Bold emphasis mine.

I ran a test to confirm that (against my former belief!) NOT NULL is completely redundant in combination with a PRIMARY KEY constraint (in the current implementation, up to version 9.5). The NOT NULL constraint stays after you drop the PK constraint, irregardless of an explicit NOT NULL clause at creation time.

db=# CREATE TEMP TABLE foo (foo_id int PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE

db=# ALTER TABLE foo DROP CONSTRAINT foo_pkey;
ALTER TABLE

db=# \d foo
   table »pg_temp_4.foo«
 column |  type   | attribute
--------+---------+-----------
 foo_id | integer | not null

Identical behaviour if NULL is included in the CREATE statement.

However, it still won't hurt to keep NOT NULL redundantly in code repositories if the column is supposed to be NOT NULL. If you later decide to move the pk constraint around, you might forget to mark the column NOT NULL - or whether it even was supposed to be NOT NULL.

There is an item in the Postgres TODO wiki to decouple NOT NULL from the PK constraint. So this might change in future versions:

Move NOT NULL constraint information to pg_constraint

Currently NOT NULL constraints are stored in pg_attribute without any designation of their origins, e.g. primary keys. One manifest problem is that dropping a PRIMARY KEY constraint does not remove the NOT NULL constraint designation. Another issue is that we should probably force NOT NULL to be propagated from parent tables to children, just as CHECK constraints are. (But then does dropping PRIMARY KEY affect children?)

Answer to added question:

Would it not be better if this self-contradictory CREATE TABLE just failed right there?

As explained above, this

foo_id INTEGER NULL PRIMARY KEY

is equivalent to:

foo_id INTEGER PRIMARY KEY

Since NULL is treated as noise word.
And we wouldn't want the latter to fail. So this is not an option.

这篇关于为什么我可以在可空列上创建一个带PRIMARY KEY的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！