postgresql – 为什么可以在可空的列上创建一个带有PRIMARY KEY

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.

大胆强调我的

我运行了一个测试,以确认(反对我以前的信念！)NOT NULL是完全冗余的,与PRIMARY KEY约束(在当前的实现中,最多为9.5版本).在删除PK约束之后,NOT NULL约束保持不变,无论创建时是否显式的NOT 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 null

CREATE语句中包含NULL的相同行为.

但是,如果列应该为NOT NULL,那么在代码存储库中冗余不会保留NOT NULL仍然不会受到伤害.如果您稍后决定移动pk约束,您可能会忘记标记列NOT NULL – 或者它是否应该为NOT NULL.

有一个item in the Postgres TODO wiki从PK约束中解耦NOT NULL.所以这可能会在以后的版本中改变：

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?)

回答补充问题：

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

如上所述,这样

foo_id INTEGER NULL PRIMARY KEY

相当于：

foo_id INTEGER PRIMARY KEY

由于NULL被视为噪声字.我们不希望后者失败.所以这不是一个选择.