0

I have two table, with a PK of ID. If say I delete the entry for ID = 1, I want it to automatically delete the FK of ID in the other table. In other words I want so that it should also delete all entries with ID = 1 in the other table. How can I do this? I have linked the PK-FK relationship, but when I delete the entry with ID 1 in the PK table it doesn't delete the FK.

aherlambang
  • 14,290
  • 50
  • 150
  • 253
  • possible duplicate of [How do I use on delete cascade in mysql?](http://stackoverflow.com/questions/511361/how-do-i-use-on-delete-cascade-in-mysql) – Dan J Mar 24 '11 at 19:06

2 Answers2

4

Make sure you're using the InnoDB engine for both tables, and add a foreign-key constraint specifying on delete cascade. Your table creation SQL should look something like this:

create table child_table (
    parent_id int references parent_table(id) on delete cascade
) engine 'innodb';

where child_table and parent_table are the names of your child and parent tables.

user229044
  • 232,980
  • 40
  • 330
  • 338
0

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

Note: You need to use InnoDB storage engine, he 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
)
Silambarasan
  • 767
  • 5
  • 19