PostgreSQL外键报错：引用表category无匹配唯一约束

我在定义process表的外键时收到错误提示：试图设为外键的列无唯一值。为此，我将category和subject表的id与name列设为复合主键，但创建process表时仍报错：

there is no unique constraint matching given keys for referenced table "category"

相关表的SQL代码如下：

category表 #

CREATE TABLE category(
    category_id  INT GENERATED ALWAYS AS IDENTITY,
    category_name VARCHAR(210),
    category_description TEXT,
    CONSTRAINT category_pk PRIMARY KEY(category_id,category_name)
);

subject表 #

CREATE TABLE subject(
    subject_id  INT GENERATED ALWAYS AS IDENTITY,
    subject_name VARCHAR(210),
    subject_description TEXT,
    CONSTRAINT subject_pk PRIMARY KEY(subject_id,subject_name)
);

我还尝试过重新添加复合主键，但仍报相同错误：

ALTER TABLE category ADD CONSTRAINT some_constraint PRIMARY KEY(category_id,category_name);

报错的process表代码 #

CREATE TABLE process(
    process_id  INT GENERATED ALWAYS AS IDENTITY,
    fk_category_id INTEGER,
    fk_subject_id INTEGER,
    FOREIGN KEY(fk_category_id) REFERENCES category(category_id) ON DELETE CASCADE ON UPDATE 
    CASCADE,
    FOREIGN KEY(fk_subject_id) REFERENCES subject(subject_id) ON DELETE CASCADE ON UPDATE 
    CASCADE
);

解决方案 #

问题核心是外键关联的字段与复合主键结构不匹配：

• category表的主键是复合主键(category_id, category_name)，仅两个字段的组合具备唯一性，单独的category_id没有唯一约束，因此无法单独作为外键关联。
• subject表的逻辑完全一致。

方案1：修改process表，关联完整复合主键 #

需要在process表中补充对应的name字段，创建复合外键匹配原表的复合主键：

CREATE TABLE process(
    process_id  INT GENERATED ALWAYS AS IDENTITY,
    fk_category_id INTEGER,
    fk_category_name VARCHAR(210),
    fk_subject_id INTEGER,
    fk_subject_name VARCHAR(210),
    -- 关联category的复合主键
    FOREIGN KEY(fk_category_id, fk_category_name) 
        REFERENCES category(category_id, category_name) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    -- 关联subject的复合主键
    FOREIGN KEY(fk_subject_id, fk_subject_name) 
        REFERENCES subject(subject_id, subject_name) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

方案2：给category_id和subject_id单独添加唯一约束 #

如果不需要复合主键的组合唯一逻辑，只需保证category_id和subject_id各自唯一，可给单个字段添加唯一约束（或直接将其设为单字段主键）：

-- 给category表的category_id添加唯一约束
ALTER TABLE category ADD CONSTRAINT category_id_unique UNIQUE(category_id);
-- 给subject表的subject_id添加唯一约束
ALTER TABLE subject ADD CONSTRAINT subject_id_unique UNIQUE(subject_id);

完成后，原process表的创建语句即可正常执行。

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