PostgreSQL自引用外键parent_id设为NULL默认值的实现问题

你之前的错误核心是给parent_id误用了SERIAL类型。在PostgreSQL里，SERIAL并非普通数据类型，它会自动创建一个序列，并把列的默认值设为该序列的nextval()结果。这就是插入数据时parent_id自动生成值的原因，也是添加DEFAULT NULL报错“multiple default values specified”的根源——SERIAL已经隐含设置了默认值，不能重复指定。

正确建表语句 #

把parent_id的类型改为integer（SERIAL本质就是integer加序列默认值），作为外键引用containers(id)即可，此时该列默认值就是NULL，无需额外声明：

CREATE TABLE containers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INTEGER REFERENCES containers(id)
);

验证效果 #

执行插入操作：

INSERT INTO containers(name) VALUES('test_name');

查询结果会符合预期：

id |   name    | parent_id
----+-----------+-----------
  1 | test_name |         NULL
(1 row)

已创建表的修改方案 #

如果表已经创建，可通过ALTER TABLE语句修改：

ALTER TABLE containers 
    ALTER COLUMN parent_id DROP DEFAULT;

注：parent_id的实际类型已经是integer（SERIAL创建的列类型就是integer），只需移除SERIAL隐含的序列默认值，即可让默认值恢复为NULL。

内容的提问来源于stack exchange，提问作者galah92