I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).
How do I make a SQL statment that DELETE ON CASCADE?
If I delete a category then how do I make sure that it would not delete products that also are related to other categories.
The pivot table "categories_products" creates a many-to-many relationship between the two other tables.
categories
- id (INT)
- name (VARCHAR 255)
products
- id
- name
- price
categories_products
- categories_id
- 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. Given your example tables, you should have the following table setup:
CREATE TABLE categories (
id int unsigned not null primary key,
name VARCHAR(255) default null
)Engine=InnoDB;
CREATE TABLE products (
id int unsigned not null primary key,
name VARCHAR(255) default null
)Engine=InnoDB;
CREATE TABLE categories_products (
category_id int unsigned not null,
product_id int unsigned not null,
PRIMARY KEY (category_id, product_id),
KEY pkey (product_id),
FOREIGN KEY (category_id) REFERENCES categories (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES products (id)
ON DELETE CASCADE
ON UPDATE CASCADE
)Engine=InnoDB;
This way, you can delete a product OR a category, and only the associated records in categories_products will die alongside. The cascade won't travel farther up the tree and delete the parent product/category table.
e.g.
products: boots, mittens, hats, coats
categories: red, green, blue, white, black
prod/cats: red boots, green mittens, red coats, black hats
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'.
The delete will not cascade any farther and will not take out the 'boots' and 'coats' categories.
comment followup:
you're still misunderstanding how cascaded deletes work. They only affect the tables in which the "on delete cascade" is defined. In this case, the cascade is set in the "categories_products" table. If you delete the 'red' category, the only records that will cascade delete in categories_products are those where category_id = red. 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.
Here's a more concrete example:
categories: products:
+----+------+ +----+---------+
| id | name | | id | name |
+----+------+ +----+---------+
| 1 | red | | 1 | mittens |
| 2 | blue | | 2 | boots |
+---++------+ +----+---------+
products_categories:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1 | 1 | // red mittens
| 1 | 2 | // blue mittens
| 2 | 1 | // red boots
| 2 | 2 | // blue boots
+------------+-------------+
Let's say you delete category #2 (blue):
DELETE FROM categories WHERE (id = 2);
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. Since we only defined the foreign key relationship in products_categories, you end up with this table once the delete completes:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1 | 1 | // red mittens
| 2 | 1 | // red boots
+------------+-------------+
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. There's just no 'blue boots' and no 'blue mittens' anymore.
I got confused by the answer to this question, so I created a test case in MySQL, hope this helps
-- Schema
CREATE TABLE T1 (
`ID` int not null auto_increment,
`Label` varchar(50),
primary key (`ID`)
);
CREATE TABLE T2 (
`ID` int not null auto_increment,
`Label` varchar(50),
primary key (`ID`)
);
CREATE TABLE TT (
`IDT1` int not null,
`IDT2` int not null,
primary key (`IDT1`,`IDT2`)
);
ALTER TABLE `TT`
ADD CONSTRAINT `fk_tt_t1` FOREIGN KEY (`IDT1`) REFERENCES `T1`(`ID`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_tt_t2` FOREIGN KEY (`IDT2`) REFERENCES `T2`(`ID`) ON DELETE CASCADE;
-- Data
INSERT INTO `T1` (`Label`) VALUES ('T1V1'),('T1V2'),('T1V3'),('T1V4');
INSERT INTO `T2` (`Label`) VALUES ('T2V1'),('T2V2'),('T2V3'),('T2V4');
INSERT INTO `TT` (`IDT1`,`IDT2`) VALUES
(1,1),(1,2),(1,3),(1,4),
(2,1),(2,2),(2,3),(2,4),
(3,1),(3,2),(3,3),(3,4),
(4,1),(4,2),(4,3),(4,4);
-- Delete
DELETE FROM `T2` WHERE `ID`=4; -- Delete one field, all the associated fields on tt, will be deleted, no change in T1
TRUNCATE `T2`; -- Can't truncate a table with a referenced field
DELETE FROM `T2`; -- This will do the job, delete all fields from T2, and all associations from TT, no change in T1
I think (I'm not certain) that foreign key constraints won't do precisely what you want given your table design. Perhaps the best thing to do is to define a stored procedure that will delete a category the way you want, and then call that procedure whenever you want to delete a category.
CREATE PROCEDURE `DeleteCategory` (IN category_ID INT)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN
DELETE FROM
`products`
WHERE
`id` IN (
SELECT `products_id`
FROM `categories_products`
WHERE `categories_id` = category_ID
)
;
DELETE FROM `categories`
WHERE `id` = category_ID;
END
You also need to add the following foreign key constraints to the linking table:
ALTER TABLE `categories_products` ADD
CONSTRAINT `Constr_categoriesproducts_categories_fk`
FOREIGN KEY `categories_fk` (`categories_id`) REFERENCES `categories` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `Constr_categoriesproducts_products_fk`
FOREIGN KEY `products_fk` (`products_id`) REFERENCES `products` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE
The CONSTRAINT clause can, of course, also appear in the CREATE TABLE statement.
Having created these schema objects, you can delete a category and get the behaviour you want by issuing CALL DeleteCategory(category_ID) (where category_ID is the category to be deleted), and it will behave how you want. But don't issue a normal DELETE FROM query, unless you want more standard behaviour (i.e. delete from the linking table only, and leave the products table alone).
Foreign key constraints and cascade delete are crucial parts of managing databases. These are especially for systems that use MySQL. We'll discuss some ideas that help keep data organized and accurate. These concepts are important for managing data well.
Foreign key rules ensure tables in a database are linked. The foreign key in one table is related to the primary key in another table. Foreign key constraints are rules that ensure data integrity between tables in a database. In a database, a foreign key column connects two tables. It links the referencing table to the referenced table to create a relationship. This helps organize the information.
Applying a foreign key constraint means a value changed in a foreign key column must match a primary key column in the referenced table. This ensures data consistency. This prevents the creation of orphaned records or inconsistencies in the data.
Let's look at two tables: "Orders" and "Customers." The "Orders" table has a column called "customer_id". "customer_id" refers to the "id" in the "Customers" table. The "customer_id" column in the "Orders" table must match an "id" in the "Customers" table.
Cascade delete is an action associated with foreign key constraints. When you delete a record in one table, the related records in the other table will also be deleted. It happens without needing manual deletion. This ensures data consistency and prevents orphaned records in the database. Cascade update is another option in foreign key constraints.
Let's say you delete a customer record in the "Customers" table. If you set a cascade delete on the "Orders" table's foreign key constraint, all the orders linked to that customer will be deleted. This simplifies data management and maintains the integrity of the database.
Cascade delete can also be applied to multiple levels of relationships. We can set a way to remove related items when an order is deleted by configuring cascade delete. This applies to the "Items" table, which has a foreign key referencing the "Orders" table. This effect goes down the relationships, deleting related records at each level.
When using cascade delete, be careful, as it may cause accidental data loss if not done properly. Think twice before turning on cascade delete. It can affect the whole system due to data dependencies you need to consider.
Foreign key constraints can use cascade update in addition to cascade delete. If you enable cascade update, a change in the primary key will also change the related foreign key values in another table. This maintains data integrity and ensures that the relationships between tables remain valid.
Foreign key constraints and cascade delete are important in MySQL databases. They keep data organized and make managing it easier. This helps maintain data accuracy. Establishing referential integrity improves database reliability and consistency.
It's important to be thoughtful when creating database structures. This includes thinking about how tables are related. We also take into account potential issues with cascade deletion. Proper planning and testing are important to use these features well in a system.
