Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as off-topic by StanleyJohns, Paul White, dezso, Marian, RolandoMySQLDBA Jul 23 '13 at 18:33

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – StanleyJohns, Paul White, dezso, Marian, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

add comment

1 Answer 1

up vote 2 down vote accepted

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;
share|improve this answer
    
Thanks so much. It helps. –  Brady Chu Jul 23 '13 at 4:26
    
This is not the answer to the problem Brady Chu is having. Try to identify the rows that have a missing reference and fix those issues. After fixing those rows (maybe link them to a dummy record if you have no other option) you can create the Foreign Key without errors. –  Ruud van de Beeten Jul 23 '13 at 7:41
    
That's what i explained above that there must be at least one row in child table that is referencing to non-existing row in parent table , so either he can fix it by entering dummy entry in in parent table or if he cant't enter the dummy entry then can supress the foreign_key_check for altering the table. –  Yogesh Ballani Jul 23 '13 at 7:54
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.