MonkeyClicker

Cannot delete or update a parent row: a foreign key constraint fails while trying to Drop a table.


I have a problem that i'm not able to drop a table. I did this a lot of times but this time it is throwing me this error as mentioned below and just not letting me to drop the table. I just want to drop the table and re-create it.Here is the error:

"Cannot delete or update a parent row: a foreign key constraint fails" 1217

Code: Select all

drop table if exists `rel_GroupPermission`;

CREATE TABLE `rel_GroupPermission` (
`PK_GROUP` int(11) default NULL,
`PK_PERMISSION` int(11) default NULL,
KEY `Group_Permission1` (`PK_GROUP`),
KEY `Group_Permission2` (`PK_PERMISSION`),
CONSTRAINT `Group_Permission1` FOREIGN KEY (`PK_GROUP`) REFERENCES `cat_group` (`PK_GROUP`) ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT `Group_Permission2` FOREIGN KEY (`PK_PERMISSION`) REFERENCES `cat_permission` (`PK_PERMISSION`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Can somebody please look at my code and help where I'm going wrong. The tables are as given below.

Thanks a bunch,

You can't drop the table because you have a foreign key referencing data in it, which is defined by this constraint in your table:

CONSTRAINT `Group_Permission1` FOREIGN KEY (`PK_GROUP`) REFERENCES `cat_group` (`PK_GROUP`) ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT `Group_Permission2` FOREIGN KEY (`PK_PERMISSION`) REFERENCES `cat_permission` (`PK_PERMISSION`) ON DELETE NO ACTION ON UPDATE CASCADE

You need to first drop that constraint before you will be able to drop the table.

I believe this command would work:

Code: Select all

ALTER TABLE rel_GroupPermission DROP FOREIGN KEY Group_Permission1;
ALTER TABLE rel_GroupPermission DROP FOREIGN KEY Group_Permission2;

Of course you will want to re-add the constraint after you re-create your table (unless you don't want it anymore).