PostgreSQL中如何引用继承列作为外键？报错解决

问题原因 #

PostgreSQL的表继承机制中，父表的唯一约束（UNIQUE）和主键约束不会自动传递给子表。你创建的user_update表中uuid字段有UNIQUE约束，但子表plant_type仅继承了uuid字段本身，并未继承该唯一约束。而外键关联要求被引用的字段必须具备唯一约束或主键约束，因此触发报错。

解决方案 #

有两种直接可行的修改方式：

方式一：在plant_type表显式添加uuid的唯一约束 #

保留原有的id主键，给继承来的uuid字段单独添加唯一约束：

CREATE TABLE user_update(
    uuid TEXT UNIQUE NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    last_update_by TEXT NOT NULL
);

CREATE TABLE plant_type(
    id SERIAL NOT NULL PRIMARY KEY,
    common_name TEXT UNIQUE NOT NULL,
    scientific_name TEXT UNIQUE,
    notes TEXT,
    -- 显式给继承的uuid字段添加唯一约束
    CONSTRAINT plant_type_uuid_unique UNIQUE(uuid)
) INHERITS (user_update);

CREATE TABLE plant(
    id SERIAL NOT NULL PRIMARY KEY,
    geom GEOMETRY(POINT, 4326) NOT NULL, 
    plant_type_uuid TEXT NOT NULL REFERENCES plant_type(uuid)
) INHERITS (user_update);

方式二：将uuid设为plant_type的主键 #

如果业务允许，可以直接把继承来的uuid作为plant_type的主键，替代原有的id字段：

CREATE TABLE user_update(
    uuid TEXT UNIQUE NOT NULL,
    last_update TIMESTAMP DEFAULT now() NOT NULL,
    last_update_by TEXT NOT NULL
);

CREATE TABLE plant_type(
    common_name TEXT UNIQUE NOT NULL,
    scientific_name TEXT UNIQUE,
    notes TEXT,
    -- 将继承的uuid设为主键
    PRIMARY KEY(uuid)
) INHERITS (user_update);

CREATE TABLE plant(
    id SERIAL NOT NULL PRIMARY KEY,
    geom GEOMETRY(POINT, 4326) NOT NULL, 
    plant_type_uuid TEXT NOT NULL REFERENCES plant_type(uuid)
) INHERITS (user_update);

说明 #

两种方式都能满足plant表与plant_type表基于uuid的一对多外键关联需求，选择哪种取决于你的业务主键设计习惯。

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