PostgreSQL引用组合主键时如何避免唯一约束匹配错误？

问题背景 #

我在pgAdmin4中创建Reporting表，SQL定义如下：

CREATE TABLE IF NOT EXISTS Reporting
(
  SEQ integer,
  Product character varying(30) NOT NULL,
  Version integer NOT NULL,
  Grade character varying(30) NOT NULL,
  *Other_Detail...*,

  CONSTRAINT pk_Reporting
    PRIMARY KEY(SEQ, Product, Version, Grade),

  CONSTRAINT fk_Reporting_ProductGrade
    FOREIGN KEY(Product, Version, Grade)
       REFERENCES Product_Grade(Product, Version, Grade)
)

该表需要引用的Product_Grade表定义为：

CREATE TABLE IF NOT EXISTS Product_Grade
(
   Product character varying(30) NOT NULL,
   Version integer NOT NULL,
   Grade character varying(30) NOT NULL,
   *Other_Detail...*,

   CONSTRAINT pk_ProductGrade
    PRIMARY KEY(Product, Version, Grade, "Sampling Point")
)

执行时PostgreSQL抛出错误：

ERROR: there is no unique constraint matching given keys for referenced table

我也曾尝试给单个字段分别定义外键约束，但同样无效：

CONSTRAINT fk_Reporting_ProductGrade1
       FOREIGN KEY(Product)
          REFERENCES Product_Grade(Product),
   CONSTRAINT fk_Reporting_ProductGrade2
       FOREIGN KEY(Version)
          REFERENCES Product_Grade(Version),
   CONSTRAINT fk_Reporting_ProductGrade3
       FOREIGN KEY(Grade)
          REFERENCES Product_Grade(Grade)

补充说明：最初误以为Product_Grade的组合主键仅包含3个字段，实际是4个字段（多了Sampling Point）。

问题原因 #

PostgreSQL对外键约束有严格要求：外键引用的字段必须对应被引用表中完整的唯一约束（或主键），或者是唯一约束的前缀字段组合。
由于Product_Grade的主键是(Product, Version, Grade, "Sampling Point")，只有这四个字段的组合才是唯一的，(Product, Version, Grade)只是主键的子集，无法保证在Product_Grade中唯一，因此数据库不允许创建这样的外键。

而单独给单个字段建外键无效的原因是：单个字段（比如Product）在Product_Grade中可能存在大量重复值，无法保证关联数据的唯一性和一致性，不符合外键约束的设计逻辑。

解决方案 #

提供两种可行的解决思路：

方案一：给Product_Grade添加唯一约束 #

如果业务上(Product, Version, Grade)的组合本身应该是唯一的，直接给Product_Grade添加该组合的唯一约束：

ALTER TABLE Product_Grade
ADD CONSTRAINT uq_product_version_grade UNIQUE (Product, Version, Grade);

添加完成后，再执行Reporting表的创建语句，外键约束就能正常生效。

方案二：修改Reporting表，引用完整主键 #

如果业务上Reporting表必须关联到Product_Grade的完整主键记录，需要在Reporting表中添加Sampling Point字段，并修改外键约束引用完整的主键组合：

CREATE TABLE IF NOT EXISTS Reporting
(
  SEQ integer,
  Product character varying(30) NOT NULL,
  Version integer NOT NULL,
  Grade character varying(30) NOT NULL,
  "Sampling Point" [替换为对应数据类型] NOT NULL, -- 需与Product_Grade表的字段类型一致
  *Other_Detail...*,

  CONSTRAINT pk_Reporting
    PRIMARY KEY(SEQ, Product, Version, Grade, "Sampling Point"),

  CONSTRAINT fk_Reporting_ProductGrade
    FOREIGN KEY(Product, Version, Grade, "Sampling Point")
       REFERENCES Product_Grade(Product, Version, Grade, "Sampling Point")
)

内容的提问来源于stack exchange，提问作者S'mon