asked on

#1452 - Cannot add or update a child row: a foreign key constraint fails

Whenever I try to create a relationship between one table and another, to create a foreign key, i get the error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`expenses`.<result 2 when explaining filename '#sql-22f8_203'>, CONSTRAINT `#sql-22f8_203_ibfk_1` FOREIGN KEY (`users_id`) REFERENCES `users` (`users_id`))

Any ideas what this could be? Googled this with no useful results. :(

Thanks.

COBOLdinosaur

Without seeing the structure of the data and the code that is generation the error, it is not likely that we can do much more than speculate.

Cd&

LB1234

ASKER

Error
SQL query:

ALTER TABLE  `transactions` ADD FOREIGN KEY (  `users_id` ) REFERENCES  `expenses`.`users` (

`users_id`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

Open in new window

LB1234

ASKER

Chris Stanyon

Because you're adding a foreign key, you need to make sure that the data in the child table already exists in the parent table. In your case, every users_id that exists in the transactions table must already exist in the users table. The datatype has to match as well.

SOLUTION

COBOLdinosaur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LB1234

ASKER

I have exactly one user in the users_id with a value of "64" (previous test users were erased). In the transactions table i have several dummy transactions and have a column called "users_id" which is populated with all zeroes. Do I change these all to "64"? The data type is the same. Thanks

LB1234

ASKER

COBOL, i'm not clear on which columns in which table your first statement refers. Can you clarify? thanks.

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LB1234

ASKER

Thanks Chris and Cobol!