How do I use on delete cascade in mysql?

Marius_1585 0
Viewed 81529

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?

cascade foreign-keys mysql sql
4 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.

nickf_90210 
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.
user_56893490
Related