I think (I'm not certain) that foreign key constraints won't do precisely what you want given your table design. TRUNCATE `T2` - Can't truncate a table with a referenced fieldĭELETE FROM `T2` - This will do the job, delete all fields from T2, and all associations from TT, no change in T1 INSERT INTO `T2` (`Label`) VALUES ('T2V1'),('T2V2'),('T2V3'),('T2V4') ĭELETE FROM `T2` WHERE `ID`=4 - Delete one field, all the associated fields on tt, will be deleted, no change in T1 I got confused by the answer to this question, so I created a test case in MySQL, hope this helps - SchemaĪDD CONSTRAINT `fk_tt_t1` FOREIGN KEY (`IDT1`) REFERENCES `T1`(`ID`) ON DELETE CASCADE,ĪDD CONSTRAINT `fk_tt_t2` FOREIGN KEY (`IDT2`) REFERENCES `T2`(`ID`) ON DELETE CASCADE There's just no 'blue boots' and no 'blue mittens' anymore. There's no foreign key defined in the products table, so the cascade will not work there, so you've still got boots and mittens listed. Since we only defined the foreign key relationship in products_categories, you end up with this table once the delete completes: +-+-+ The DBMS will look at all the tables which have a foreign key pointing at the 'categories' table, and delete the records where the matching id is 2. Let's say you delete category #2 (blue): DELETE FROM categories WHERE (id = 2) Here's a more concrete example: categories: products: It won't touch any records where 'category_id = blue', and it would not travel onwards to the "products" table, because there's no foreign key defined in that table. If you delete the 'red' category, the only records that will cascade delete in categories_products are those where category_id = red. In this case, the cascade is set in the "categories_products" table. They only affect the tables in which the "on delete cascade" is defined. You're still misunderstanding how cascaded deletes work. The delete will not cascade any farther and will not take out the 'boots' and 'coats' categories. If you delete the 'red' category, then only the 'red' entry in the categories table dies, as well as the two entries prod/cats: 'red boots' and 'red coats'. Prod/cats: red boots, green mittens, red coats, black hats products: boots, mittens, hats, coatsĬategories: red, green, blue, white, black The cascade won't travel farther up the tree and delete the parent product/category table.Į.g. This way, you can delete a product OR a category, and only the associated records in categories_products will die alongside. Given your example tables, you should have the following table setup: CREATE TABLE categories (įOREIGN KEY (category_id) REFERENCES categories (id)įOREIGN KEY (product_id) REFERENCES products (id) If your cascading deletes nuke a product because it was a member of a category that was killed, then you've set up your foreign keys improperly.
0 Comments
Leave a Reply. |