Foreign keys and stored procedures
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
Subject
Views
Written By
Posted
Foreign keys and stored procedures
3487
July 29, 2008 06:31AM
1617
July 29, 2008 07:59PM
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.