I’m not a professional DBA, I encountered a problem when I’s attempting to create FOREIGN KEY for a specific table by the following SQL script (I’m using MySQL):
ALTER TABLE mmy_answer ADD CONSTRAINT fk_answer_userId FOREIGN KEY (userId) references mmy_user_account(id);
I’m sure there is no FOREIGN KEY named ‘fk_answer_userId’ with table mmy_answer and refered table mmy_user_account exists, the following is the error:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`marry0528`.<result 2 when explaining filename '#sql-734_5'>, CONSTRAINT `fk_answer_userId` FOREIGN KEY (`userId`) REFERENCES `mmy_user_account` (`id`))
Can anybody give me help? Thanks.
That means you have at least one row in the child table that references a non-existent row in the parent table.
If you are absolutely sure that you are okay with having a data integrity issue like that, you can add the foreign key by disabling foreign key checks before you run the ALTER TABLE command:
SET FOREIGN_KEY_CHECKS = 0;
And after altering the table and adding constraints set
SET FOREIGN_KEY_CHECKS = 1;