postgresql 为什么我可以在可为空的列上创建带有 PRIMARY KEY 的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20006374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can I create a table with PRIMARY KEY on a nullable column?
提问by A-K
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:
请注意，我无法按预期插入 NULL：
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?
为什么我可以创建一个定义自相矛盾的表？ID 列显式声明为 NULLable，并且作为 PRIMARY KEY 的一部分，它隐式不可为 null。是否有意义？
Edit: would it not be better if this self-contradictory CREATE TABLE just failed right there?
编辑：如果这个自相矛盾的 CREATE TABLE 就在那里失败会不会更好？
回答by Erwin Brandstetter
Because the PRIMARY KEYmakesthe column NOT NULLautomatically. I quote the manual here:
由于PRIMARY KEY使列NOT NULL自动。我在这里引用手册：
The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically,
PRIMARY KEYis merely a combination ofUNIQUEandNOT NULL.
主键约束指定表的一列或多列只能包含唯一（非重复）、非空值。从技术上来说，
PRIMARY KEY仅仅是一个组合UNIQUE和NOT NULL。
Bold emphasis mine.
大胆强调我的。
I ran a test to confirm that (against my former belief!) NOT NULLis completely redundant in combination with a PRIMARY KEYconstraint (in the current implementation, up to version 9.5). The NOT NULL constraint staysafter you drop the PK constraint, irregardless of an explicit NOT NULLclause at creation time.
我运行了一个测试来确认（与我以前的信念相反！）NOT NULL结合PRIMARY KEY约束（在当前实现中，直到版本 9.5）是完全多余的。该NOT NULL约束停留后，你放弃了PK的约束，明确的irregardlessNOT NULL在创建时的条款。
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 nullIdentical behaviour if NULLis included in the CREATEstatement.
如果语句中NULL包含相同的行为CREATE。
However, it still won't hurt to keep NOT NULLredundantly 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.
但是，NOT NULL如果该列应该是NOT NULL. 如果您稍后决定移动 pk 约束，您可能会忘记标记该列NOT NULL- 或者它是否应该是NOT NULL.
There is an item in the Postgres TODO wikito decouple NOT NULLfrom the PK constraint. So this might change in future versions:
Postgres TODO wiki 中有一个项目可以与NOT NULLPK 约束解耦。所以这可能会在未来的版本中改变：
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?)
将 NOT NULL 约束信息移动到 pg_constraint
目前，NOT NULL 约束存储在 pg_attribute 中，没有任何指定它们的来源，例如主键。一个明显的问题是删除 PRIMARY KEY 约束不会删除 NOT NULL 约束指定。另一个问题是我们可能应该强制 NOT NULL 从父表传播到子表，就像 CHECK 约束一样。（但是删除 PRIMARY KEY 会影响孩子吗？）
Answer to added question:
回答添加的问题：
Would it not be better if this self-contradictory CREATE TABLE just failed right there?
如果这个自相矛盾的 CREATE TABLE 就在那里失败了不是更好吗？
As explained above, this
如上所述，这
foo_id INTEGER NULL PRIMARY KEY
is equivalent to:
相当于：
foo_id INTEGER PRIMARY KEY
Since NULLis treated as noise word.
And we wouldn't want the latter to fail. So this is not an option.
因为NULL被视为干扰词。
我们不希望后者失败。所以这不是一个选择。
回答by Denis de Bernardy
If memory serves, the docs mention that:
如果没记错的话，文档会提到：
- the
nullin create table statements is basically a noise word that gets ignored - the
primary keyforces a not null and a unique constraint
- 将
null在创建表的语句基本上是被忽视的干扰词 - 该
primary key部队NOT NULL和唯一约束
See:
看：
# create table test (id int null primary key);
CREATE TABLE
# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
回答by Marcio Mazzucato
If as @ErwinBrandstetter said, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, you can use an UNIQUEconstraint without NOT NULLinstead of PRIMARY KEY. Example:
如果正如@ErwinBrandstetter 所说，PRIMARY KEY 只是 UNIQUE 和 NOT NULL 的组合，您可以使用UNIQUE不使用而NOT NULL不是的约束PRIMARY KEY。例子：
CREATE TABLE test(
id integer,
CONSTRAINT test_id_key UNIQUE(id)
);
This way you can do things like:
通过这种方式，您可以执行以下操作：
INSERT INTO test (id) VALUES (NULL);
INSERT INTO test (id) VALUES (NULL);
INSERT INTO test (id) VALUES (NULL);
