如何禁止SQLite中consumable_items与weapons同时引用同一条items记录？

我创建了三张表，结构如下：

CREATE TABLE "items" (
    "key"   TEXT NOT NULL UNIQUE,
    "name_key"  TEXT,
    "base_price"    INTEGER,
    "description_key"   TEXT,
    PRIMARY KEY("key")
)

CREATE TABLE "consumable_items" (
    "key"   TEXT NOT NULL UNIQUE,
    "item_key"  TEXT NOT NULL,
    "scope" TEXT NOT NULL,
    "only_menu" BOOLEAN NOT NULL,
    PRIMARY KEY("key"),
    FOREIGN KEY("item_key") REFERENCES "items"("key")
)

CREATE TABLE "weapons" (
    "key"   TEXT NOT NULL UNIQUE,
    "item_key"  TEXT NOT NULL,
    "name_key"  TEXT,
    "type"  TEXT,
    FOREIGN KEY("item_key") REFERENCES "items"("key"),
    FOREIGN KEY("type") REFERENCES "weapon_types"("key"),
    PRIMARY KEY("key")
)

consumable_items和weapons通过外键item_key引用items表，实现物品公共属性的复用，现在需要实现约束：一条items记录只能被其中一张表引用，不能同时出现在consumable_items和weapons中。

比如插入这条items记录后：

INSERT INTO "main"."items" ("key", "name_key", "base_price", "description_key") VALUES ('excalibur', 'excalibur', '800000', 'excalibur');

该记录只能被consumable_items或weapons中的一个引用，不能同时存在两条关联记录。

实现方案：使用触发器（TRIGGER） #

SQLite本身没有原生的互斥外键约束，但可以通过触发器实现这一逻辑，需要创建4个触发器覆盖INSERT和UPDATE操作：

1. 插入consumable_items时检查冲突

CREATE TRIGGER prevent_consumable_if_weapon_exists
BEFORE INSERT ON consumable_items
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, '该物品已被武器表引用，无法添加到消耗品表')
    WHERE EXISTS (
        SELECT 1 FROM weapons WHERE item_key = NEW.item_key
    );
END;

2. 更新consumable_items的item_key时检查冲突

CREATE TRIGGER prevent_consumable_update_if_weapon_exists
BEFORE UPDATE OF item_key ON consumable_items
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, '该物品已被武器表引用，无法更新消耗品表关联')
    WHERE EXISTS (
        SELECT 1 FROM weapons WHERE item_key = NEW.item_key
    );
END;

3. 插入weapons时检查冲突

CREATE TRIGGER prevent_weapon_if_consumable_exists
BEFORE INSERT ON weapons
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, '该物品已被消耗品表引用，无法添加到武器表')
    WHERE EXISTS (
        SELECT 1 FROM consumable_items WHERE item_key = NEW.item_key
    );
END;

4. 更新weapons的item_key时检查冲突

CREATE TRIGGER prevent_weapon_update_if_consumable_exists
BEFORE UPDATE OF item_key ON weapons
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, '该物品已被消耗品表引用，无法更新武器表关联')
    WHERE EXISTS (
        SELECT 1 FROM consumable_items WHERE item_key = NEW.item_key
    );
END;

测试验证 #

1. 先插入基础物品：

INSERT INTO "items" ("key", "name_key", "base_price", "description_key") VALUES ('excalibur', 'excalibur', '800000', 'excalibur');

2. 插入到weapons表：

INSERT INTO "weapons" ("key", "item_key", "name_key", "type") VALUES ('wp_excalibur', 'excalibur', 'excalibur', 'sword');

执行成功。

3. 尝试插入到consumable_items表：

INSERT INTO "consumable_items" ("key", "item_key", "scope", "only_menu") VALUES ('cm_excalibur', 'excalibur', 'single', 1);

此时触发器触发，抛出错误：该物品已被武器表引用，无法添加到消耗品表，插入操作被终止。

注意事项 #

• 触发器会在操作执行前触发，确保数据一致性；
• 自定义错误信息可根据需求修改；
• 删除关联表中的记录后，对应的item_key会自动解除限制，允许被另一张表引用。

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