MySQL Forums
Forum List  »  InnoDB

Foreign keys and stored procedures
Posted by: Manish Srivastava
Date: July 29, 2008 06:31AM

Hello,

I am hoping that someone can help me out with a problem I am having with foreign keys. I am trying to delete a parent row after I have deleted all possible children using a stored procedure and I am getting a Foreign key constraint violation: Error code 1451.

Here are the specifics:

drop database test;
create database test;

CREATE TABLE `test`.`groups`
(
`groupID` bigint(20) unsigned NOT NULL auto_increment,
PRIMARY KEY (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `test`.`group_user`
(
`group_id` bigint(20) unsigned,
CONSTRAINT `fk_group_user_group_id` FOREIGN KEY (`group_id`) REFERENCES `test.groups` (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test`.`groups` VALUES (1),(2);

INSERT INTO `test`.`group_user` VALUES (1),(2);

delimiter $$;

drop procedure if exists test.deleteGroup$$

create procedure test.deleteGroup(groupID BIGINT)
begin

delete from test.group_user where group_id=groupID;
delete from test.groups where groupid=groupID;

end$$

delimiter ;$$

call test.deleteGroup(1);

The error I am getting is:

Error Code : 1451
Cannot delete or update a parent row: a foreign key constraint fails (`test/group_user`, CONSTRAINT `fk_group_user_group_id` FOREIGN KEY (`group_id`) REFERENCES `groups` (`groupID`))

Obviously this is happening when I am trying to delete the data out of the test.group table even though there should not be any children.

when I try the following statements it seems to work fine:
delete from test.group_user where group_id=1;
delete from test.groups where groupid=1;

any ideas?
manish

Options: ReplyQuote


Subject
Views
Written By
Posted
Foreign keys and stored procedures
3487
July 29, 2008 06:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.