PostgreSQL继承表外键引用异常：插入Sale数据触发约束报错

你创建的表结构如下：

CREATE TABLE Product ( 
  "Product_id" int, 
  "Stock_quantity" int, 
  "Product_name" varchar(50), 
  "Model" varchar(50), 
  "Average_rating" float(3), 
  "RAM" int, 
  "Color" varchar(20), 
  "Price" float(10), 
  PRIMARY KEY ("Product_id") 
); 

CREATE TABLE Sale ( 
  "Sale_id" int, 
  "Sale_date" date, 
  "Employee_id" int, 
  "Customer_id" int, 
  "Product_id" int, 
  "Product_quantity" int, 
  "Rating" int, 
  PRIMARY KEY ("Sale_id"), 
  FOREIGN KEY("Employee_id") REFERENCES Employee("Employee_id"), 
  FOREIGN KEY("Customer_id") REFERENCES Customer("Customer_id"), 
  FOREIGN KEY("Product_id") REFERENCES Product("Product_id") 
); 

CREATE TABLE Computer ( 
  "Type" varchar(10), 
  "Processor" varchar(20), 
  "Monitor_size" int 
) inherits(Product); 

CREATE TABLE Mobile ( 
  "Os" varchar(30), 
  "Screen_size" int 
) inherits(Product); 

你遇到的问题是：所有产品数据都插入到Computer或Mobile子表中，未直接插入Product主表，但向Sale表插入数据时触发外键约束报错：

ERROR: insert or update on table "sale" violates foreign key constraint "PK_Product_id"
SQL state: 23503
Detail: Key (Product_id)=(12) is not present in table "product".

明明查询Product表能看到Product_id=12的记录，却报错不存在，这是PostgreSQL表继承的一个特性导致的，下面给你详细解释和解决办法：

问题原因 #

PostgreSQL的表继承有个容易踩的坑：外键约束默认只会校验父表本身的记录，不会包含其继承的子表数据。

你查询SELECT * FROM Product能看到子表的数据，是因为PostgreSQL默认查询父表时会自动包含所有子表的内容；但外键约束的校验逻辑是只看ONLY Product（也就是父表本身）的记录，而你所有产品数据都存在子表里，父表Product本身并没有这条Product_id=12的记录，所以才会触发约束报错。

可行的解决办法 #

方法1：用约束触发器替代原生外键约束 #

原生外键不支持跨继承表校验，我们可以自己写一个约束触发器来实现全继承层次的校验：

1. 先删除原来的外键约束（如果不知道约束名，可以用\d Sale查看）：

ALTER TABLE Sale DROP CONSTRAINT sale_product_id_fkey;

2. 创建一个检查产品是否存在的函数：

CREATE OR REPLACE FUNCTION check_product_exists()
RETURNS TRIGGER AS $$
BEGIN
  -- 查询Product会自动包含所有子表的数据
  IF NOT EXISTS (
    SELECT 1 FROM Product WHERE "Product_id" = NEW."Product_id"
  ) THEN
    RAISE EXCEPTION 'Product_id % does not exist in any product table', NEW."Product_id";
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建约束触发器，在插入/更新Sale表时触发校验：

CREATE CONSTRAINT TRIGGER check_product_fk
AFTER INSERT OR UPDATE ON Sale
FOR EACH ROW EXECUTE FUNCTION check_product_exists();

这样以后插入Sale数据时，就会检查Product_id是否存在于任何产品表（父表+子表）中，完全符合你的需求。

方法2：改用分区表替代表继承 #

如果你的产品分类场景适合用分区表（比如按产品类型分区），可以把Product改成分区表，Computer和Mobile作为它的分区。PostgreSQL的分区表在处理外键约束时，会自动检查所有分区的数据，这样原来的外键约束就能正常工作，不需要额外修改。

方法3：同步子表数据到父表（不推荐） #

你可以每次向子表插入数据时，同时在父表插入对应的Product_id记录，让外键约束能找到匹配项。为了避免手动同步的错误，我们可以用触发器自动完成：

1. 创建同步父表的函数：

CREATE OR REPLACE FUNCTION sync_parent_product()
RETURNS TRIGGER AS $$
BEGIN
  -- 插入父表，已存在则忽略
  INSERT INTO Product("Product_id") VALUES(NEW."Product_id")
  ON CONFLICT ("Product_id") DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 给子表添加触发器：

CREATE TRIGGER sync_computer_to_parent
AFTER INSERT ON Computer
FOR EACH ROW EXECUTE FUNCTION sync_parent_product();

CREATE TRIGGER sync_mobile_to_parent
AFTER INSERT ON Mobile
FOR EACH ROW EXECUTE FUNCTION sync_parent_product();

不过这种方法会导致父表产生冗余数据，而且如果子表删除数据，父表的记录不会自动删除，容易出现数据不一致，所以除非特殊情况，不推荐使用。

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