Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I'm having a bit of a strange problem. I'm trying to add a foreign key to one table that references another, but it is failing for some reason. With my limited knowledge of MySQL, the only thing that could possibly be suspect is that there is a foreign key on a different table referencing the one I am trying to reference.

I've done a SHOW CREATE TABLE query on both tables, sourcecodes_tags is the table with the foreign key, sourcecodes is the referenced table.

CREATE TABLE `sourcecodes` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `language_id` int(11) unsigned NOT NULL,
 `category_id` int(11) unsigned NOT NULL,
 `title` varchar(40) CHARACTER SET utf8 NOT NULL,
 `description` text CHARACTER SET utf8 NOT NULL,
 `views` int(11) unsigned NOT NULL,
 `downloads` int(11) unsigned NOT NULL,
 `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `language_id` (`language_id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
CREATE TABLE `sourcecodes_tags` (
 `sourcecode_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`sourcecode_id`),
 KEY `tag_id` (`tag_id`),
 CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This is the code that generates the error:
ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE
                try running this query to see if there is any sourcecode_id that is not a real id: SELECT sourcecode_id FROM sourcecodes_tags WHERE sourcecode_id NOT IN (SELECT id FROM sourcecodes AS tmp);
– Zed
                Aug 10, 2009 at 7:22
                Thanks Zed, that was the problem one of the tables had data in it. Thinking about it now it makes sense that it was failing because there were things that were referencing non-existing items, but I never would have guessed that. Thanks!
– Zim
                Aug 10, 2009 at 7:26
Quite likely your sourcecodes_tags table contains sourcecode_id values that no longer exists in your sourcecodes table. You have to get rid of those first.
Here's a query that can find those IDs:
SELECT DISTINCT sourcecode_id FROM 
   sourcecodes_tags tags LEFT JOIN sourcecodes sc ON tags.sourcecode_id=sc.id 
WHERE sc.id IS NULL;
                UPDATE sourcecodes_tags SET sourcecode_id = NULL WHERE sourcecode_id NOT IN (SELECT id FROM sourcecodes) should help to get rid of those IDs. Or if null is not allowed in sourcecode_id, then remove those rows or add those missing values to the sourcecodes table.
– naXa stands with Ukraine
                Feb 23, 2016 at 14:28
                I was thinking the same but, for me SELECT Tchild.id FROM Tchild INNER JOIN Tmain ON Tmain.id = Tchild.fk_id WHERE Tmain.id IS NULL doesn't return anything, so the problem is elsewhere !?
– Meloman
                Jul 26, 2017 at 13:35
                Ahh this was the problem for me. I was trying to run UPDATE `homestead`.`automations` SET `deleted_at`=NULL WHERE deleted_at IS NOT NULL;, which didn't involve a foreign key at all, so I was confused. But the fact that my contacts table was missing some records that the automations table referred to caused it to throw this "Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails".
– Ryan
                Jan 25, 2019 at 22:43
I had the same issue with my MySQL database but finally, I got a solution which worked for me.

Since in my table everything was fine from the mysql point of view(both tables should use InnoDB engine and the datatype of each column should be of the same type which takes part in foreign key constraint).

The only thing that I did was to disable the foreign key check and later on enabled it after performing the foreign key operation.

Steps that I took:
SET foreign_key_checks = 0;
alter table tblUsedDestination add constraint f_operatorId foreign key(iOperatorId) references tblOperators (iOperatorId); Query
OK, 8 rows affected (0.23 sec) Records: 8  Duplicates: 0  Warnings: 0
SET foreign_key_checks = 1;
                foreign_key_checks are there for a reason.  If you can't add the foreign key because it violates the constraint, you should correct the data first.  Turning off checks then adding the key leaves you in an inconsistent state.  Foreign key checks add overhead, if you do not want to use them, then use myisam instead.
– cs_alumnus
                Mar 19, 2014 at 22:44
                @AbuSadatMohammedYasin no it shouldn't: the question asked "what is going on" and this answer simply doesn't attempt to explain it. As cs_alumnus mentioned, there's a bigger problem: all the new values that should be referencing another value in the other table (as a Foreign key should do) may point to nothing, creating an inconsistent state. Cayetano's short and effective explanation allows you to find which values you should update before creating the constraint so you won't be surprised by queries that should return values that should exist!
– Armfoot
                Nov 19, 2015 at 22:13
                In my case the parent had column length 25 chars while the children tables had 30 chars. Increasing chars length to 30 fixed it for me. Thanks for pointing me in the right direction.
– Omar Dulaimi
                Jun 23, 2021 at 1:29
Use NOT IN to find where constraints are constraining:
SELECT column FROM table WHERE column NOT IN 
(SELECT intended_foreign_key FROM another_table)
so, more specifically:
SELECT sourcecode_id FROM sourcecodes_tags WHERE sourcecode_id NOT IN 
(SELECT id FROM sourcecodes)
EDIT: IN and NOT IN operators are known to be much faster than the JOIN operators, as well as much easier to construct, and repeat.
                So, if I understand this correctly, we can add a foreign key to a table that already has data in it, but only if a child row exists for each row in the parent table?  If there are no child rows for each row in the parent table (which is what your query discovers) then the foreign key script will fail.
– Vincent
                Jan 16, 2015 at 19:05
                @Vincent if by parent table you mean the table being referenced, then yes! Therefore with Cayetano's select you get all rows you need to update/remove from your "child" table before you add the new constraint (FK). Once they all point to values in the "another_table" then you're good to go!
– Armfoot
                Nov 19, 2015 at 22:21
                No need to truncate everything. "UPDATE sourcecodes_tags SET sourcecode_id = NULL WHERE sourcecode_id NOT IN (SELECT id FROM sourcecodes)" should suffice. Or if null is not allowed in "sourcecode_id", then remove those rows or add those missing values to the "sourcecodes" table.
– Torben
                Aug 19, 2013 at 10:45
                @ShankarDamodaran not sure why truncating the table works but this solution worked well for me. I was able to get my relationships to work... THANKS!
– MizAkita
                Apr 11, 2014 at 3:59
                @MizAkita it works because it deletes the rows that have no corresponding value in the other table, allowing the new constraint to be created. If you just find those rows and update or delete them (like Cayetano's suggestion), you don't need to delete the other rows...
– Armfoot
                Nov 20, 2015 at 10:34
                @Armfoot - I had this problem when adding first row to the table with foreign key. So I had no rows to search for.
– Krewetka
                May 6, 2016 at 8:41
For me, this problem was a little different and super easy to check and solve. 
You must ensure BOTH of your tables are InnoDB. If one of the tables, namely the reference table is a MyISAM, the constraint will fail.
    SHOW TABLE STATUS WHERE Name =  't1';
    ALTER TABLE t1 ENGINE=InnoDB;
This also happens when setting a foreign key to parent.id to child.column if the child.column has a value of 0 already and no parent.id value is 0
You would need to ensure that each child.column is NULL or has value that exists in parent.id
And now that I read the statement nos wrote, that's what he is validating.
I had the same problem today. I tested for four things, some of them already mentioned here:
Are there any values in your child column that don't exist in the parent column (besides NULL, if the child column is nullable)
Do child and parent columns have the same datatype?
Is there an index on the parent column you are referencing? MySQL seems to require this for performance reasons (http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html)
And this one solved it for me: Do both tables have identical collation? 
I had one table in UTF-8 and the other in iso-something. That didn't work. After changing the iso-table to UTF-8 collation the constraints could be added without problems. In my case, phpMyAdmin didn't even show the child table in iso-encoding in the dropdown for creating the foreign key constraint.
It seems there is some invalid value for the column line 0 that is not a valid foreign key so MySQL cannot set a foreign key constraint for it.
You can follow these steps:
Drop the column which you have tried to set FK constraint for.
Add it again and set its default value as NULL.
Try to set a foreign key constraint for it again.
SET foreign_key_checks = 0;
ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE
SET foreign_key_checks = 1;
                VHanded gave the same answer 3 years ago. Let's hope there wasn't any important data in the tables...
– xlecoustillier
                Apr 23, 2015 at 9:02
I had this exact same problem about three different times. In each instance it was because one (or more) of my records did not conform to the new foreign key. You may want to update your existing records to follow the syntax constraints of the foreign key before trying to add the key itself. The following example should generally isolate the problem records:
SELECT * FROM (tablename)
    WHERE (candidate key) <> (proposed foreign key value) 
        AND (candidate key) <> (next proposed foreign key value)
repeat AND (candidate key) <> (next proposed foreign key value) within your query for each value in the foreign key.
If you have a ton of records this can be difficult, but if your table is reasonably small it shouldn't take too long. I'm not super amazing in SQL syntax, but this has always isolated the issue for me.
I was getting this error when using Laravel and eloquent, trying to make a foreign key link would cause a 1452. The problem was lack of data in the linked table.
Please see here for an example: http://mstd.eu/index.php/2016/12/02/laravel-eloquent-integrity-constraint-violation-1452-foreign-key-constraint/ 
You just need to answer one question:
Is your table already storing data? (Especially the table included foreign key.)
If the answer is yes, then the only thing you need to do is to delete all the records, then you are free to add any foreign key to your table.
  
Delete instruction: From child(which include foreign key table) to parent table.
The reason you cannot add in foreign key after data entries is due to the table inconsistency, how are you going to deal with a new foreign key on the former data-filled the table?
If the answer is no, then follow other instructions.
I was readying this solutions and this example may help.
My database have two tables (email and credit_card) with primary keys for their IDs. Another table (client) refers to this tables IDs as foreign keys. I have a reason to have the email apart from the client data.
First I insert the row data for the referenced tables (email, credit_card) then you get the ID for each, those IDs are needed in the third table (client). 
If you don't insert first the rows in the referenced tables, MySQL wont be able to make the correspondences when you insert a new row in the third table that reference the foreign keys.
If you first insert the referenced rows for the referenced tables, then the row that refers to foreign keys, no error occurs.
Hope this helps.
                mysql> insert into email (email) values ('xxx@yyy.com'); mysql> insert into ndtc (ndtc, year, month) values ('1111222233334444', '2000', '01'); mysql> insert into cliente (nombres, apellidos, telefono, idNDTC, idEmail) values ('myname', 'myapp', '5555555555', 1,1);
– CesareoAguirre
                Mar 16, 2016 at 1:44
Make sure the value is in the other table otherwise you will get this error, in the assigned corresponding column.
So if it is assigned column is assigned to a row id of another table , make sure there is a row that is in the table otherwise this error will appear.
 START TRANSACTION;
 SET foreign_key_checks = 0;
 ALTER TABLE `job_definers` ADD CONSTRAINT `job_cities_foreign` FOREIGN KEY 
 (`job_cities`) REFERENCES `drop_down_lists`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 SET foreign_key_checks = 1;
 COMMIT;
Note : if you are using phpmyadmin just uncheck Enable foreign key checks
as example 
hope this soloution fix your problem :)
I had the same problem and found solution, placing NULL instead of NOT NULL on foreign key column. Here is a query:
ALTER TABLE `db`.`table1`
ADD COLUMN `col_table2_fk` INT UNSIGNED NULL,
ADD INDEX `col_table2_fk_idx` (`col_table2_fk` ASC),
ADD CONSTRAINT `col_table2_fk1`
FOREIGN KEY (`col_table2_fk`)
REFERENCES `db`.`table2` (`table2_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
MySQL has executed this query!
In my case, I created a new table with the same structure, created the relationships with the other tables, then extracted the data in CSV from the old table that has the problem, then imported the CSV to the new table and disabled foreign key checking and disabled import interruption, all my data are inserted to the new table that has no problem successfully, then deleted the old table.
It worked for me.