Question

The following setup will:

Allow me to delete existing entries
Prevent me from creating new entries in products

这可能是因为条目是在<编码>产品之前在<编码>上设定的。但是,我不能在<条码>类别中编造条目。在此之前,我知道该产品。

What would you do?

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

www.un.org/Depts/DGACM/index_spanish.htm 每个入境点的顺序为。

Create an entry in categories
Create an entry in product
Using the IDs from 1. and 2. to create a relationship between categories and products

Answer 1

我认为,你的制约因素落后。该外国钥匙应列入<编码>类别——产品表格,并注明<编码>产品表,而不是以下表格:

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

Answer 2

页: 1

Each product can have many categories.
Each category can have many 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;

您也不妨加上“儿童成长”条款,这将有助于你从母系中删除行文,并自动删除儿童表中的相应行文。例如,下个查询将删除<编码>分类-产品上的自动相关记录。表:

DELETE FROM category id = 100;

友情链接