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?
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.
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.
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.
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.
use this sql
DELETE T1, T2 FROM T1 INNER JOIN T2 ON T1.key = T2.key WHERE condition
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
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With