List:General Discussion« Previous MessageNext Message »
From:Derek Knapp Date:June 17 2011 5:33pm
Subject:cascade delete question
View as plain text  
I have the following 3 tables.. If I have a contact with just notes (no 
tasks), then I can simply do

delete from contacts where id = xxxx;

but if the contact has a task, then I get the following error, ERROR 
1451 (23000): Cannot delete or update a parent row: a foreign key 
constraint fails (`task`, CONSTRAINT `fk_task_2` FOREIGN KEY (`noteid`) 
REFERENCES `note` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE)

to get this to work, I have to do

delete from task where contactid = xxxx;
delete from contacts where id = xxxx;

I understand the problem, but I would have thought mysql would be smart 
enough to figure this out, and allow the delete to proceed (since its 
going to cascade the tasks eventually)

is this normal?  is there any way to specify the order which it 
cascades, if it were to delete the tasks before the notes, this would 
not be a problem...





CREATE TABLE  `contacts` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   ...
   PRIMARY KEY (`id`) USING BTREE
)

CREATE TABLE  `note` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `contactid` bigint(20) unsigned NOT NULL,
   ...
   PRIMARY KEY (`id`) USING BTREE,
   KEY `contactid` (`contactid`),
   CONSTRAINT `fk_note_1` FOREIGN KEY (`contactid`) REFERENCES 
`contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `task` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `contactid` bigint(20) unsigned NOT NULL,
   `noteid` bigint(20) unsigned NOT NULL,
   ...
   PRIMARY KEY (`id`) USING BTREE,
   KEY `contactid` (`contactid`) USING BTREE,
   KEY `noteid` (`noteid`) USING BTREE,
   CONSTRAINT `fk_task_1` FOREIGN KEY (`contactid`) REFERENCES 
`contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `fk_task_2` FOREIGN KEY (`noteid`) REFERENCES `note` 
(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
)
Thread
cascade delete questionDerek Knapp17 Jun