How do I use on delete cascade in mysql?

Question

I have a database of components. Each component is of a specific type. That means there is a many-to-one relationship between a component and a type. When I delete a type, I would like to delete all the components which has a foreign key of that type. But if I'm not mistaken, cascade delete will delete the type when the component is deleted. Is there any way to do what I described?

score 59 · Accepted Answer · answered Feb 04 '09 at 13:37

59

Here's what you'd include in your components table.

CREATE TABLE `components` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `typeId` int(10) unsigned NOT NULL,
    `moreInfo` VARCHAR(32), 
    -- etc
    PRIMARY KEY (`id`),
    KEY `type` (`typeId`)
    CONSTRAINT `myForeignKey` FOREIGN KEY (`typeId`)
      REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

Just remember that you need to use the InnoDB storage engine: the default MyISAM storage engine doesn't support foreign keys.

answered Feb 04 '09 at 13:37

nickf

537,072
198
649
721

5

N.B., ON UPDATE CASCADE is probably a bad idea if you're using it to link a primary key because it should be immutable and relying on this suggests database might require redesign, but I guess if you're linking it with a foreign key that's unique but not primary it's ok. – ThinkBonobo Dec 28 '13 at 03:09

score 5 · Answer 2 · answered Jul 25 '16 at 08:14

5

use this sql

DELETE T1, T2 FROM T1 INNER JOIN T2 ON T1.key = T2.key WHERE condition

answered Jul 25 '16 at 08:14

md asif rahman

389
3
9

This does not use the `CASCADE` functionality as asked by op. – Stefan Falk Aug 02 '16 at 14:58
12

But it does solve the problem without updating the existing database structure. – Flip Vernooij Sep 02 '16 at 13:21
Can you expand on this answer? Will this delete the rows from both T1 and T2? Does order matter in "T1 INNER JOIN T2"? – David Faure Jul 31 '19 at 09:31

Silambarasan · Answer 3 · 2019-09-30T10:17:27.237

You have to define your Foreign Key constraints as ON DELETE CASCADE.

Note: You need to use InnoDB storage engine, the default MyISAM storage engine not support foreign keys relation.

CREATE TABLE `table2` (
`id` int(11) NOT NULL auto_increment,
`name` int(11) NOT NULL,

PRIMARY KEY (`id`),
KEY `ids` (`ids`)
CONSTRAINT `foreign` FOREIGN KEY (`ids`)
  REFERENCES `table2` (`ids`) ON DELETE CASCADE ON UPDATE CASCADE
)

score 0 · Answer 4 · answered Oct 28 '20 at 04:02

Step 1. Create the buildings table:

CREATE TABLE buildings (
  building_no INT PRIMARY KEY AUTO_INCREMENT,
  building_name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

Step 2. Create the rooms table:

CREATE TABLE rooms (
  room_no INT PRIMARY KEY AUTO_INCREMENT,
  room_name VARCHAR(255) NOT NULL,
  building_no INT NOT NULL,
  FOREIGN KEY (building_no)
    REFERENCES buildings (building_no)
    ON DELETE CASCADE
);

Notice that the ON DELETE CASCADE  clause at the end of the foreign key constraint 
definition.

How do I use on delete cascade in mysql?

4 Answers4

Linked

Related