CODEWITHSUNDEEP
mysqlforeign-keys
Sherin Jose
Sherin Jose

Reputation: 2526

Can't add foreign key

I m trying to add a foreign key to table table2 from table1. But i got an error like this... what is the problem...help

Error

SQL query:

ALTER TABLE `table2` ADD FOREIGN KEY ( `bus_id` ) REFERENCES `db1`.`table1` (
`bus_id`
) ON DELETE CASCADE ON UPDATE CASCADE ;

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`db1`.`#sql-664_e2`, CONSTRAINT `#sql-664_e2_ibfk_1` FOREIGN KEY (`bus_id`) REFERENCES `table1` (`bus_id`) ON DELETE CASCADE ON UPDATE CASCADE)

Structure of table1

enter image description here

Structure of table2

enter image description here

Upvotes: 0

Views: 195

Answers (3)

Devart
Devart

Reputation: 121922

Your tables have some data. Check that all values stored in table2.bus_id are correct; all these values have to be in table1.bus_id.

Run this query to view wrong bus_id values in table2:

SELECT t2.bus_id AS inconsistent_bus_id FROM table2 t2
LEFT JOIN table1 t1
  ON t1.bus_id = t2.bus_id
WHERE t1.bus_id IS NULL

Remove this records from table2, or add this bus_id to the table1. Then try to create foreign key again.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

I can't see any error on your ALTER statement. The main reason is that maybe there are already records existed on table2 which bus_id are not on table1. So the best solution you can do is to empty first table2 and run the alter statement again.

Upvotes: 1

rkosegi
rkosegi

Reputation: 14658

To get more info run following command:

SHOW ENGINE INNODB STATUS;

I expect that there is data mismatch, eg. table2 contain values in column bus_id which is not found in table1.

try this:

SELECT DISTINCT bus_id FROM table2 WHERE 
    bus_id NOT IN (SELECT bus_id FROM table1)

Also make sure that both tables uses InnoDB as storage engine.

Upvotes: 1

Related Questions