CODEWITHSUNDEEP
mysqlsqlforeign-keys
heron
heron

Reputation: 3661

MySQL strange FK error

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?

Upvotes: 0

Views: 134

Answers (2)

Devart
Devart

Reputation: 121902

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.

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65537

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.

Upvotes: 1

Related Questions