CODEWITHSUNDEEP
phpmysql
dapperwaterbuffalo
dapperwaterbuffalo

Reputation: 2748

MySQL Error: Cannot add or update a child row: a foreign key constraint fails

So the scenario is this:

I create a group with a form, stored in test_groups_tb, then with another form enter user ids to attach them to the group and they are stored in group_association_tb with a structure like so:

group_id | user_id

I am very new to MySQL and more so Foreign Key relationships, so I tried to set up a relationship on group_association_tb which was basically, if the group was deleted, delete all records of that group from group_association_tb.

now what Is happening since applying this is the group is stored fine. I can add students manually fine, but when I try adding them by importing a csv it doesn't like it.

here is the queries (i know its not efficient querying every iteration):

while($row = mysql_fetch_assoc($result))
    {
        $sql = "INSERT INTO group_association_tb (group_id, user_id)VALUES('$group','".$row[user_id]."')";
        mysql_query($sql) or die(header("Location:error.php"));
    }

here is the error i get:

Cannot add or update a child row: a foreign key constraint fails (`ece70141/group_association_tb`, CONSTRAINT `group_association_tb_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `test_groups_tb` (`group_id`) ON DELETE CASCADE)

could somebody explain what the problem is here.

many thanks,

Upvotes: 0

Views: 4842

Answers (2)

superultranova
superultranova

Reputation: 1314

EDIT: I think that you need to invert the constraint. test_groups_tb needs to reference the grop_association_tbl.

It looks like you have not added the group_id that you are referencing from the group_association_tb table to the test_group_tb table. If a foreign key from group_association_tb references a key in test_group_tb, the key has to be in test_group_tb before you can add it to group_association_tb.

For instance:

I have a table (user_group)_ defined as follows:

group_id, first_name, last_name

And another (group_permission) as follows:

group_id, permission_name

if I create a foreign key from group_permission that references user_group, I cannot add a row to group_permission with a group_id that doesn't exist in user_group already. First, I have to create the row in user_group with a given group_id, then insert into the group_permission table with the group_id of the associated row in user_group.

Upvotes: 0

AR.
AR.

Reputation: 1955

For those encountering the problem " ERROR 1216: Cannot add or update a child row: a foreign key constraint fails", it actually means what it says! Some row in the child does not comply with the constraint, correct the problem. You find the rows like this: select child.id from child left join parent on (child.parent_id=parent.id) where child.id is not null and parent.id is null;

(from http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html)

Upvotes: 1

Related Questions