为何PostgreSQL标识列会触发外键非唯一错误？

问题场景 #

在PostgreSQL 14.3中尝试创建以下两张表：

CREATE TABLE IF NOT EXISTS foo (
  id INT4 GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 1 MINVALUE -2147483648 START -2147483648),
  foo TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS bar (
  id INT4 GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 1 MINVALUE -2147483648 START -2147483648),
  foo_id INT4 NOT NULL REFERENCES foo(id)
);

执行后得到错误输出：

$ psql test -f bug01.sql
CREATE TABLE
psql:bug01.sql:9: ERROR:  there is no unique constraint matching given keys for referenced table "foo"

原以为GENERATED BY DEFAULT AS IDENTITY能满足唯一性约束，请问遗漏了什么？该如何解决？

原因分析 #

PostgreSQL的GENERATED BY DEFAULT AS IDENTITY仅负责让列通过序列自动生成值，但不会自动为该列添加主键约束或唯一约束。而外键引用的核心要求是：被引用的列必须拥有唯一约束（主键约束是包含非空特性的特殊唯一约束），以此保证引用的目标是唯一的行。

foo表的id列虽然是IDENTITY列，但没有任何唯一约束，数据库无法确保id值的唯一性，因此bar表的外键创建会失败。

解决方案 #

为foo表的id列添加主键约束（也可以添加UNIQUE NOT NULL约束，主键是更符合语义的选择），修正后的SQL如下：

CREATE TABLE IF NOT EXISTS foo (
  id INT4 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 1 MINVALUE -2147483648 START -2147483648),
  foo TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS bar (
  id INT4 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 1 MINVALUE -2147483648 START -2147483648),
  foo_id INT4 NOT NULL REFERENCES foo(id)
);

执行后成功创建两张表：

$ psql test -f bug01.sql
CREATE TABLE
CREATE TABLE

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