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
)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
