SQL插入同时检查其中一个键是否等于另一个表中的字段

Question

我正在使用 sequelize 和 postgresql，但我认为这是一个更通用的 SQL/表问题。

我的设置类似于:

CREATE TABLE "Mixtime" (
    id bigint NOT NULL,
    duration character varying(255) NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    spell_id bigint NOT NULL,
    ingredient_id bigint NOT NULL,
    user_id bigint NOT NULL
);

CREATE TABLE "Spell" (
    id bigint NOT NULL,
    instructions character varying(5000) NOT NULL,
    created_at timestamp with time zone NOT NULL,
    user_id bigint NOT NULL
);

CREATE TABLE "Ingredient" (
    id bigint NOT NULL,
    ing_name character varying(255) NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    user_id bigint NOT NULL
);

CREATE TABLE "Users" (
    id bigint NOT NULL,
    user_name character varying(255) NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
);

ALTER TABLE ONLY "Mixtime"
    ADD CONSTRAINT "Mixtime_pkey" PRIMARY KEY (id);

ALTER TABLE ONLY "Spell"
    ADD CONSTRAINT "Spell_pkey" PRIMARY KEY (id);

ALTER TABLE ONLY "Ingredient"
    ADD CONSTRAINT "Ingredient_pkey" PRIMARY KEY (id);

ALTER TABLE ONLY "Users"
    ADD CONSTRAINT "Users_pkey" PRIMARY KEY (id);

ALTER TABLE ONLY "Mixtime"
    ADD CONSTRAINT "Mixtime_spell_id_fkey" FOREIGN KEY (spell_id) REFERENCES "Spell"(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY "Mixtime"
    ADD CONSTRAINT "Mixtime_ingredient_id_fkey" FOREIGN KEY (ingredient_id) REFERENCES "Ingredient"(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY "Mixtime"
    ADD CONSTRAINT "Mixtime_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "Users"(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY "Spell"
    ADD CONSTRAINT "Spell_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "Users"(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY "Ingredient"
    ADD CONSTRAINT "Ingredient_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "Users"(id) ON UPDATE CASCADE ON DELETE CASCADE;

我想做的是确保当我插入 Mixtime 时 user_id 与 Spell & Ingredients 的 user_id 字段相匹配

伪代码:

If (newMixtime.userId != Spell.user_id || newMixtime.userId != Ingredient.user_id) {
  failHere
} else {
  insert newMixtime into M
}

请注意，这不是连接表。所有三个表都需要通过 user_id 字段进行查询，并且表 Mixtime 具有特定的额外字段，它只是引用 Spell & Ingredient。

我可以(并且目前正在)通过先查询数据库来在 ORM 层进行验证，但这似乎在数据库层应该是可行的并且可以节省我的行程。

如果您知道如何将其映射到 Sequlize 的模型语法中，那就太好了，但如果我有一个纯 postgres/SQL 解决方案，我可能会弄明白。

Answer 1

完全规范化的表集不会此约束。表示此信息的最简单方法是，每个表中的每一行都由恰好一个父表的一行“拥有”，由单个外键表示。除非您迫切需要在一个用户的不同步骤/法术之间共享成分而不是在不同用户之间共享它们，否则您不需要在成分上使用 user_id 字段。

真正的多表约束(外键除外)很难准确可靠地实现。您可以使用触发器，但它们往往是非常特定于引擎的，我不建议将您的逻辑放入触发器中，除非真的别无选择。