Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I use on delete cascade in mysql?

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?

like image 464
Marius Avatar asked Feb 04 '09 13:02

Marius


People also ask

How does Cascade on delete work?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.

Does MySQL support cascade delete?

ON DELETE CASCADE clause in MySQL is used to automatically remove the matching records from the child table when we delete the rows from the parent table. It is a kind of referential action related to the foreign key.

Should I use on delete cascade?

ON DELETE CASCADE is fine, but only when the dependent rows are really a logical extension of the row being deleted. For example, it's OK for DELETE ORDERS to delete the associated ORDER_LINES because clearly you want to delete this order, which consists of a header and some lines.


3 Answers

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.

like image 162
nickf Avatar answered Sep 22 '22 03:09

nickf


use this sql

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

like image 31
md asif rahman Avatar answered Sep 23 '22 03:09

md asif rahman


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
)
like image 2
Silambarasan Avatar answered Sep 24 '22 03:09

Silambarasan