关联数据集外键设置问题：视图关联报错的替代实现方案咨询

外键约束仅支持引用表的主键或唯一约束列，视图无法作为外键的引用目标，以下是几种可行的替代方案：

方案1：生成列 + 多层外键约束（推荐，需SQLite 3.31.0+） #

利用SQLite的生成列特性，通过class关联lookup表得到对应的code，再结合color约束关联costs表的唯一键组合：

CREATE TABLE products (
  class TEXT NOT NULL,
  color TEXT NOT NULL,
  price REAL,
  -- 从lookup表自动生成对应code
  code TEXT GENERATED ALWAYS AS (
    (SELECT code FROM lookup WHERE lookup.class = products.class)
  ) STORED,
  UNIQUE(class),
  -- 约束class必须存在于lookup表
  FOREIGN KEY (class) REFERENCES lookup(class),
  -- 约束(code, color)组合必须存在于costs表
  FOREIGN KEY (code, color) REFERENCES costs(code, color)
);

插入数据时会自动触发两层检查：

• 若class不在lookup中直接报错；
• 若class存在但对应code+color组合不在costs中，同样触发外键错误，完全匹配你的需求。

方案2：触发器实现自定义约束（兼容旧版SQLite） #

如果你的SQLite版本不支持生成列，可通过触发器手动实现验证逻辑：

CREATE TABLE products (
  class TEXT NOT NULL,
  color TEXT NOT NULL,
  price REAL,
  UNIQUE(class)
);

-- 插入前验证约束
CREATE TRIGGER check_product_cost_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
  -- 检查class是否存在于lookup
  SELECT RAISE(ABORT, 'Class not found in lookup')
  WHERE NOT EXISTS (SELECT 1 FROM lookup WHERE class = NEW.class);
  
  -- 检查对应class+color是否有匹配的成本数据
  SELECT RAISE(ABORT, 'No matching cost for this class and color')
  WHERE NOT EXISTS (
    SELECT 1 FROM costs
    JOIN lookup ON costs.code = lookup.code
    WHERE lookup.class = NEW.class AND costs.color = NEW.color
  );
END;

-- 更新前同样验证约束
CREATE TRIGGER check_product_cost_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'Class not found in lookup')
  WHERE NOT EXISTS (SELECT 1 FROM lookup WHERE class = NEW.class);
  
  SELECT RAISE(ABORT, 'No matching cost for this class and color')
  WHERE NOT EXISTS (
    SELECT 1 FROM costs
    JOIN lookup ON costs.code = lookup.code
    WHERE lookup.class = NEW.class AND costs.color = NEW.color
  );
END;

方案3：新增中间关联表（适合需频繁复用约束场景） #

创建一个存储有效class+color组合的中间表，让products直接引用该表：

-- 创建中间表，存储所有合法的class+color组合
CREATE TABLE valid_product_cost (
  class TEXT NOT NULL,
  color TEXT NOT NULL,
  code TEXT NOT NULL,
  PRIMARY KEY (class, color),
  FOREIGN KEY (class) REFERENCES lookup(class),
  FOREIGN KEY (code, color) REFERENCES costs(code, color)
);

-- 初始化中间表数据（从lookup和costs关联生成）
INSERT INTO valid_product_cost (class, color, code)
SELECT lookup.class, costs.color, costs.code
FROM lookup
JOIN costs ON lookup.code = costs.code;

-- 修改products表引用中间表
CREATE TABLE products (
  class TEXT NOT NULL,
  color TEXT NOT NULL,
  price REAL,
  UNIQUE(class),
  FOREIGN KEY (class, color) REFERENCES valid_product_cost(class, color)
);

注意：此方案需在lookup或costs数据变更时，手动同步更新valid_product_cost表，否则会出现数据不一致。

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