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

Question

以下设置将：

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

这可能是因为条目是在categories_products之前在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中的ID。和2。创建categories和products之间的关系

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

您有一个多对多关系：

• 每种产品可以有许多类别
• 每个类别可以有许多产品

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

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;