CODEWITHSUNDEEP
mysql
Symfony
Symfony

Reputation: 2439

Cannot add foreign key in InnoDB

In phpMyAdmin when i try to add foreign key some error occurs and does not tell what is wrong. Just says "FK fails".

Can any one tell what is the problem?

Error

SQL query:

ALTER TABLE `hotel` ADD FOREIGN KEY ( `type_id` ) REFERENCES `hotel`.`hotel_type` (
`id`
) ON DELETE CASCADE ON UPDATE CASCADE ;

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`hotel`.`#sql-cfc_e`, CONSTRAINT `#sql-cfc_e_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `hotel_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Upvotes: 2

Views: 4655

Answers (5)

Nigel Greenway
Nigel Greenway

Reputation: 383

Although this is old, I thought I would share that the way I resolved this was by checking that the encoding and the collation were the same on both sides which for some reason were different.

This fixed the issue.

Upvotes: 0

Joseph
Joseph

Reputation: 6032

Remove the ON DELETE CASCADE ON UPDATE CASCADE

Upvotes: 0

Alireza
Alireza

Reputation: 6868

There are some data in your table that no longer exist in other table, and actually this is against foreign key rules.

If you don't need their data, truncate both tables, and add the foreign key otherwise you should add all the foreign keys one by one.

Here is a query that will find those rows:

select sourcecode_id from

sourcecodes_tags tags left join sourcecodes sc on tags.sourcecode_id=sc.id

where sc.id is null;

Upvotes: 1

mgm
mgm

Reputation: 143

Well there can be a lot of reasons for which you can't create a foreign key. I have found a very interesting article, which I suggest you to read it closely. Maybe something will help you. You can also find very important infos in the MySQL manual, regarding the "FOREIGN KEY Constraints".

Hope this helped.

Upvotes: 2

Mike Purcell
Mike Purcell

Reputation: 19999

Be sure the columns of both tables are of the same data-type:

hotel.hotel type_id int(11) unsigned # type_id column of hotel table

hotel.hotel_type id int(11) unsigned # id column of hotel_type table

If they are not of the same type then you won't be able to add your FK constraint.

-- Edit --

Based on your response, the columns are the same data-types, so that means you have an invalid value in the hotels.type_id column (value doesn't exist in the hotel_types table). Check the values in your hotels.type_id column and make sure they exist in your hotel_types.id column.

Upvotes: 6

Related Questions