MySQL Add foreign key to existing table PHP

Question

I have a built database with all tables set using INNODB and Foreign Keys. Now I need to modify a single table and add a new column which references another table. My query is:

ALTER TABLE test ADD newcol INT NOT NULL;

ALTER TABLE test ADD CONSTRAINT fk_test
FOREIGN KEY (newcol) 
REFERENCES othertable(id);

I get the following error:

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

All the other tables contain data, but I know that if I drop the test table and create it with foreign key it will work. If I drop test table it will delete a lot of records and I want to avoid copying data and re-inserting it.

Can anyone show me how to add a new column via a foreign key?

WebChemist · Accepted Answer · 2013-02-10 01:25:40Z

1

That error message means you have values in newcol that do not exist in othertable id column. By adding a column of INT NOT NULL, you set all the initial values for that column to 0. If you do not have an id of 0 in othertable, you have a key mismatch

Try this query:

SELECT  newcol 
FROM    test
WHERE   newcol NOT IN (
    SELECT  id
    FROM    othertable
)

if you get any results returned, then you have key values in newcol that violate the FK restraints

answered Feb 10, 2013 at 1:25

WebChemist

4,4116 gold badges30 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GGio Over a year ago

what should i do to fix it so that i add foreign key

WebChemist Over a year ago

you need to update the test newcol values so that they correspond to the values within othertable id column.

Collectives™ on Stack Overflow

MySQL Add foreign key to existing table PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related