CREATE TABLE test( ID INTEGER NULL,CONSTRAINT PK_test PRIMARY KEY(ID) )
请注意,我无法插入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).
为什么我可以创建一个具有自相矛盾的定义的表？ ID列被显式声明为NULLable,并且它是隐式不可为空,作为PRIMARY KEY的一部分.是否有意义？
编辑：如果这个自相矛盾的CREATE TABLE刚刚失败,那会不会更好？
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.
大胆强调我的
我运行了一个测试,以确认(反对我以前的信念！)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被视为噪声字.我们不希望后者失败.所以这不是一个选择.
