Monday, March 28, 2011

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?

From stackoverflow
  • 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.

  • Nice Tip at d end of solution ... Can u tel me y did dey use 2 storage engine ?

    Shervin : This should be comment, and please write correct english

0 comments:

Post a Comment