Cannot create FOREIGN KEY for a table [closed]

Posted on

Question :

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.

Answer :

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:


And after altering the table and adding constraints set


Leave a Reply

Your email address will not be published. Required fields are marked *