0

Here are 2 tables.

Full size image

enter image description here

I'm trying to create relationship between them by creating foreign keys courses:parent<->child.parent_cid <=> courses.id and courses:parent<->child.child_cid <=> courses.id

SQL looks like that

ALTER TABLE `courses: parent<->child` ADD CONSTRAINT `cpc.parent_cid_courses.id` FOREIGN KEY (`parent_cid`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `courses: parent<->child` ADD CONSTRAINT `cpc.child_cid_courses.id` FOREIGN KEY (`child_cid`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Getting this error message

enter image description here

What am I doing wrong? Any suggestions?

heron
  • 3,611
  • 25
  • 80
  • 148
  • 1
    It looks like you are experiencing the same problem addressed here: http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa. Try inspecting your data to see if there are records causing the constraint to fail. – Thomas Jul 25 '12 at 14:33

2 Answers2

1

My first suggestion: rename the child table and the foreign key constraints using only alphanumeric characters and underscores.

The error message implies that there are invalid foreign key values in the child table. You can tell the MySQL server to ignore those values like this before running the ALTER TABLE statements:

set foreign_key_checks = 0;

Or you can fix the data by either adding the missing parent rows or deleting the invalid child rows before adding the constraints.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • Renamed no success. "set foreign_key_checks = 0" just to query this? – heron Jul 25 '12 at 14:43
  • The renaming suggestion won't help with this error, but it will help you down the line to use simple table, column, and constraint names. Just because MySQL allows you to create bad names (as long as you escape them) doesn't mean you should do it. – Ike Walker Jul 25 '12 at 14:47
  • As for `set foreign_key_checks=0;` that's a system variable that controls whether the MySQL server checks the existing values. You can temporarily disable it if you want, but I recommend fixing the data instead. – Ike Walker Jul 25 '12 at 14:48
1

You are trying to add foreign keys. The error means that child table has data which doesn't exist in parent table.

In your case cpc.parent_cid_courses.id.parent_cid has wrong values, there are no corresponding values in parent field courses.id.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Really don't get anything. The tables are not empty. Do you mean this? – heron Jul 25 '12 at 14:41
  • Yes, I mean this. MySQL doesn't allow creating new foreign key if there is a problem with data. – Devart Jul 25 '12 at 14:42
  • they must be empty in order to create foreign key? – heron Jul 25 '12 at 14:43
  • No, tables can have data; e.g.: if parent table contains 1,2,3, then child table may have these values, but child table cannot have 4,5,6 and so on. – Devart Jul 25 '12 at 14:46