使用外键删除多个表中的条目

Question

以下设置将：

• 允许我删除现有条目
• 阻止我在 products 中创建新条目

这可能是因为之前在 products 中创建了条目categories_products。但在知道产品 ID 之前，我无法在 categories_products 中创建条目。

你会怎么办？

ALTER TABLE products
ADD CONSTRAINT FK_products
FOREIGN KEY (id) REFERENCES categories_products(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE;

表格

categories:
id, name

categories_products:
category_id, product_id

products:
id, name

创建每个条目的顺序

1. 在类别中创建条目
2. 在产品中创建条目
3. 使用 1. 和 2. 中的 ID 创建类别之间的关系< /code> 和 产品

Answer 1

我认为你的约束是倒退的。该外键应该位于 categories_products 表中并引用 products 表，而不是相反：

ALTER TABLE categories_products
ADD CONSTRAINT FK_products
FOREIGN KEY (product_id) REFERENCES products(id)
ON DELETE CASCADE;

Answer 2

您有一个多对多关系：

• 每个产品可以有多个类别。
• 每个类别可以有很多产品。

因此，您应该添加两个外键来支持这种类型的关系。这些外来必须添加到categories_products表中：

ALTER TABLE categories_products
  ADD CONSTRAINT fk_products FOREIGN KEY (product_id)
    REFERENCES products(id),
  ADD CONSTRAINT fk_categories FOREIGN KEY (category_id)
    REFERENCES categories(id);

然后，要删除类别和相关产品，请使用如下查询：

SET FOREIGN_KEY_CHECKS = 0;

DELETE c, cp, p
  FROM category c
    JOIN categories_products cp
      ON cp.category_id = c.id
    JOIN products p
      ON p.id = cp.product_id
WHERE
  c.id = 100;

SET FOREIGN_KEY_CHECKS = 1;

您还可能需要添加ON DELETE CASCADE子句，它将帮助您从父表中删除行并自动删除子表中的匹配行。例如，下一个查询将自动删除 categories_products 表中的相关记录：

DELETE FROM category id = 100;