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

Question

table #1:

Field	Type	Null	Key	Default	Extra
UserID	int(11)	NO	PRI	NULL	auto_increment
Password	varchar(20)	NO
Username	varchar(25)	NO
Email	varchar(60)	NO

table #2:

Field	Type	Null	Key	Default	Extra
UserID	int(11)	NO	MUL
PostID	int(11)	NO	PRI	NULL	auto_increment
Title	varchar(50)	NO
Summary	varchar(500)	NO

Error:

com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (myapp/table2, CONSTRAINT table2_ibfk_1 FOREIGN KEY (UserID) REFERENCES table1 (UserID))

What have I done wrong? I read http://www.w3schools.com/Sql/sql_foreignkey.asp and I don't see what's wrong.

In an nutshell, you are trying to insert/update a value in `table2.UserID` that does not exist in `table1.UserID`. — Joe Stefanelli, Feb 15 '11 at 15:18
Not sure why but after moving my database from a windows environment to Linux, I had to delete and recreate a relation (that's when I noticed the issue). The value did exist, but removing and re-adding the relation fixed it. You may need to be pretty careful doing so obviously. — johnsnails, Jul 07 '15 at 06:07

score 297 · Answer 1 · answered Feb 15 '11 at 15:16

297

You're getting this error because you're trying to add/update a row to table2 that does not have a valid value for the UserID field based on the values currently stored in table1. If you post some more code I can help you diagnose the specific cause.

answered Feb 15 '11 at 15:16

Brian Driscoll

19,373
3
46
65

1

PreparedStatement st = connection.prepareStatement("Insert into table2 (UserID, PostID, Title, Summary)" + "values (UserID, ?, ?, ?)"); – Tom Feb 15 '11 at 15:19
22

not sure what the downvote is for; my answer is perfectly valid and correct. – Brian Driscoll Feb 15 '11 at 15:20
Hmm... it's definitely not a good idea to mix named parameters with unnamed parameters in your prepared statement. it should be "values (?, ?, ?, ?)" with the appropriate value for UserID specified in your execute statement. – Brian Driscoll Feb 15 '11 at 15:24
So I need to do to another SQL statement to get the userid from table1? – Tom Feb 15 '11 at 15:25
Yes - the value you specify for UserID must be a value that currently exists in table1. – Brian Driscoll Feb 15 '11 at 15:25
If I create a select statement to get the userid, do i store that in a variable? If so, how? Basically how do i pass the userid from the select statement to the insert statement? – Tom Feb 15 '11 at 15:36
Yes, you'd store the value of UserID from your select statement in a variable. I'm not sure what the Java syntax would be for getting the value from the select query into a variable, unfortunately. nonetheless, you'd store that value in a var and then put that var into the execute statement for your insert. – Brian Driscoll Feb 15 '11 at 15:59
6

Most likely a 0 , replace it by Null if it's the case – Jeffrey Nicholson Carré Mar 15 '13 at 21:03

score 137 · Answer 2 · edited Jun 30 '15 at 10:56

137

It means that you're trying to insert into table2 a UserID value that doesn't exist in table1.

edited Jun 30 '15 at 10:56

ᴍᴇʜᴏᴠ

4,804
4
44
57

answered Feb 15 '11 at 15:22

Rami C

1,903
1
12
14

1

I had this experience when I was adding a foreign key with ID 0, which cannot reference to the key in other table as the table does not have the record with ID 0 – samheihey Mar 26 '21 at 08:17
im having this same issue, im trying to add a new entity that has id 0 – Vinicius Bassi Sep 14 '22 at 00:01

score 125 · Answer 3 · edited Oct 30 '20 at 14:30

125

A simple hack can be to disable foreign key checks before performing any operation on the table. Simply query

SET FOREIGN_KEY_CHECKS=0

This will disable foreign key matching against any other tables. After you are done with the table enable it again

SET FOREIGN_KEY_CHECKS=1

This works for me a lot of times.

Please note that you enter the DANGER ZONE when you do this. While there are certainly valid use cases, you should only do this when you are certain you understand the implications.

edited Oct 30 '20 at 14:30

Dennis Haarbrink

3,738
1
27
54

answered Oct 20 '15 at 13:50

Sandeep Giri

1,318
1
9
4

How I USE this?? – Sachin from Pune Aug 03 '17 at 09:50
2

@SachinfromPune just add SET FOREIGN_KEY_CHECKS=0; at starting of mysql file and SET FOREIGN_KEY_CHECKS=1; at end of mysql file, reimport data – inrsaurabh Sep 15 '17 at 12:26
3

Isn't this problematic? You lose referential integrity, right? – roadrunner66 Sep 12 '19 at 21:10
Referential integrity is supported by the presence of the correct index entries. As long as you've got your keys and indexes right, you should be able to disable the key check during a bulk input. – Vernon Keenan Jan 28 '20 at 20:23

score 58 · Answer 4 · edited Apr 01 '18 at 02:56

58

I discovered another weird case: If you accidentally create a foreign key from an InnoDB table to a MyISAM table, MySQL throws this error at time of insert even if the data is otherwise valid.

See http://nick.zoic.org/art/mysql-foreign-key-error/

edited Apr 01 '18 at 02:56

Babar Al-Amin

3,939
1
16
20

answered Aug 27 '15 at 11:45

NickZoic

7,575
3
25
18

2

Yes, I have the same problem. Two tables should be InnoDB! – emeraldhieu Dec 25 '15 at 04:03
3

I had the same problem, thanks for the tip. Here's a link to MySQL documentation on how to convert tables from MyISAM to InnoDB https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html TLDR: ALTER TABLE table_name ENGINE=InnoDB; – Guilherme Vaz Sep 25 '17 at 20:41
1

Indeed, this was the case in my database. I decided to change MyISAM engine to InnoDB. Finally, I can work with the database the way I wanted to. – Mike Dec 11 '19 at 12:44
1

I spent hours looking for an answer, finally thank you! – Gilad Dahan Aug 19 '21 at 14:12

score 19 · Answer 5 · answered Aug 20 '14 at 14:26

You're getting this error because there are some value int table2.UserID that is not exists on table1.UserID (I guess that you have setted table2.UserID value manualy before you created this foreign key).
One example for this scene: table1.UserID get values 1,2,3 and table2.UserID get values 4 (add by manual). So when you make a foreign key, they can't find UserID = 4 from table1 and the error will ocurse.
To fix this error, just remove UserID = 4 from table2 or you can empty both of them and then create the foreign key and.
Good luck!

score 16 · Answer 6 · answered Jul 11 '19 at 12:57

This took me a while to figure out. Simply put, the table that references the other table already has data in it and one or more of its values does not exist in the parent table.

e.g. Table2 has the following data:

UserID    PostID    Title    Summary
5         1         Lorem    Ipsum dolor sit

Table1

UserID    Password    Username    Email
9         ********    JohnDoe     john@example.com

If you try to ALTER table2 and add a foreign key then the query will fail because UserID=5 doesn't exist in Table1.

score 10 · Answer 7 · answered Jun 16 '14 at 11:17

If you have inserted a row into table 1 before creating the foreign key in table 2, then you will get a foreign key constraint error, because the auto increment value is 2 in table 1 and 1 in table 2. To solve this you have to truncate table 1 and set the auto increment value back to 1. Then you can add table 2.

Aman Maurya · Answer 8 · 2018-10-11T10:27:32.110

10

Make sure you have set database engine to InnoDB because in MyISAM foreign key and transaction are not supported

edited Oct 11 '18 at 10:27

answered May 05 '16 at 19:05

Aman Maurya

1,305
12
26

2

ALTER TABLE my_table ENGINE = InnoDB; – Bishwas Mishra May 28 '19 at 06:16

score 10 · Answer 9 · answered Dec 26 '18 at 14:41

10

Just a little bit fix: Make the JoinColumn 'nullable = true' in Table1 and 'UserID' field 'insertable=false' and 'nullable=true' in Table2.

In Table1 Entity:

@OneToMany(targetEntity=Table2.class, cascade = CascadeType.ALL)
@JoinColumn(name = "UserID", referencedColumnName = "UserID", nullable = true)
private List<Table2> table2List;

In Table2 Entity:

@Column(insertable = false, nullable = true)
private int UserID;

answered Dec 26 '18 at 14:41

Ujjwal Chaudhary

101
1
2

This answer totally solved my problem, thank you so much for sharing this. The key for me was adding the @Column(insertable=false, nullable=true) on the filed that i am using as the foraeing key in my child object/table. – Israelm Feb 12 '19 at 00:16

score 6 · Answer 10 · answered Sep 24 '16 at 17:48

6

I just had the same problem the solution is easy.

You are trying to add an id in the child table that does not exist in the parent table.

check well, because InnoDB has the bug that sometimes increases the auto_increment column without adding values, for example, INSERT ... ON DUPLICATE KEY

answered Sep 24 '16 at 17:48

Blaztix

1,223
1
19
28

seriously? **do not insert an id reference that does not exist** ... just check – Blaztix Sep 19 '17 at 17:44

score 5 · Answer 11 · answered Sep 05 '16 at 15:11

I had a similar issue. You are trying to apply foreign key on a table which has content and the column is not nullable. You have two options.

Make the column you want to apply foreign key constraints on to be nullable. That way the foreign key will apply knowing that some fields can be nullable. (This is what i did.)
Create the column you want to apply foreign key constraint on, write a query to insert the foreign key into the column and then apply the foreign key constraints. (Did not try this but it should work)

score 4 · Answer 12 · edited Jan 29 '18 at 07:40

4

Make sure the value that you are inserting into the foreign key exists in the parent table. That helped me. For example if you insert user_id = 2 into table.2, but table.1 does not have a user_id = 2, then the constraint will throw an error. Mine was error code #1452 to be exact. Hope this helps anyone else with the same problem!

edited Jan 29 '18 at 07:40

clemens

16,716
11
50
65

answered Jan 29 '18 at 07:19

Dustin Hammack

154
1
1
15

score 2 · Answer 13 · answered Jun 28 '18 at 07:52

2

I had the same issue, and the reason was that I had a row in the first table before adding the foreign key.

answered Jun 28 '18 at 07:52

Ouissal

1,519
2
18
36

score 2 · Answer 14 · answered Feb 10 '20 at 11:24

2

Delete indexes of the UserID field of table2. Its suits for me

answered Feb 10 '20 at 11:24

Anton

21
1

score 1 · Answer 15 · answered Feb 24 '16 at 17:40

I also got this error: "Cannot add or update a child row: a foreign key constraint fails". I got the error when adding a new row to the parent table

The problem was that the foreign key constraint had been defined on the parent table instead of the child table.

score 1 · Answer 16 · answered Dec 27 '17 at 17:30

1

I also faced same issue and the issue was my parent table entries value not match with foreign key table value. So please try after clear all rows..

answered Dec 27 '17 at 17:30

Justine Jose

130
2
7

The constraint requires that users referenced in Table2 be already defined in Table1. – sorak Dec 27 '17 at 17:57

score 1 · Answer 17 · answered Oct 26 '19 at 13:35

In case if the solutions provided by others didn't work. Then you should try checking the Database Engines of the Parent and Child Tables.In my case, I had the parent tables' engine set to "MyISAM", changing it to InnoDB fixed it.

Hope this helps others who are stuck like me.

score 1 · Answer 18 · answered Oct 29 '19 at 20:35

1

You should not put an ondelete field against a cascade in the database.

So set the onDelete field to RESTRICT

Good luck ♥

answered Oct 29 '19 at 20:35

HamidReza Biglari

11
1

score 0 · Answer 19 · edited May 23 '17 at 12:26

Yet another weird case that gave me this error. I had erroneously referenced my foreign keys to the id primary key. This was caused by incorrect alter table commands. I found this out by querying the INFORMATION_SCHEMA table (See this stackoverflow answer)

The table was so confused it could not be fixed by any ALTER TABLE commands. I finally dropped the table and reconstructed it. This got rid of the integrityError.

score 0 · Answer 20 · answered Jan 07 '16 at 13:41

0

Maybe whilst you added the userID column, there is a data for that certain table that it is established so it will have a default value of 0, try adding the column without the NOT NULL

answered Jan 07 '16 at 13:41

WTFZane

592
1
4
25

score 0 · Answer 21 · answered May 05 '17 at 20:55

0

İf you use mysql index or relation between tables, firstly you delete the colums(for example:city_id) and create new colums with same name(for example:city_id).Then try again...

answered May 05 '17 at 20:55

Mahmut Aydın

831
13
21

score 0 · Answer 22 · answered Oct 01 '18 at 02:42

Is there any existing data that the table contains? If so, try to clear out all the data in the table you want to add a foreign key. Then run the code (add a foreign key) again.

I encountered this problem so many times. This clearing out the all data in the table works when you want to add foreign key on a existing table.

Hope this works :)

score 0 · Answer 23 · answered Jul 18 '20 at 16:58

That error occurs when you want to add a foreign key with values that don't exist in the primary key of the parent table. You must be sure that the new foreign key UserID in table2 has values that exist in the table1 primary key, sometimes by default it is null or equal to 0.

You could first update all the fields of the foreign key in table2 with a value that exists in the primary key of table1.

update table2 set UserID = 1 where UserID is null

If you want to add different UserIDs you must modify each row with the values you want.

score 0 · Answer 24 · answered Aug 05 '22 at 10:06

0

If you added the column with checked box NOT NULL You will have surprise to see all rows have value = 0 for that column, When you try to add foreign key maybe you don't have a row in foreign table2, with value 0 so mysql error popup show

answered Aug 05 '22 at 10:06

BoBiTza

98
13

score 0 · Answer 25 · answered Apr 28 '23 at 05:58

Thanks @BrianDriscoll,

Personally the problem originated from the Form select field.

This is how i had done it

<select name="gender" class="form-select" required>
<option selected>Select Gender </option>
<?php foreach($gender as $g):?>
<option value="<?= $g->id"><?= $g->name;?>
</option>
<?php endif;?>
</select>

So this line "<option selected>Select Gender </option>" kept giving that error everytime I had not selected that field.

Therefore changed it to "<option value="">Select Gender </option>".

Infact when I rectified it, even the required started working.

score -1 · Answer 26 · edited Dec 26 '17 at 19:08

-1

child table foreign key constraint is failing

This issue may rise due to following reason:

If you are doing it in Spring mvc, you need to explicitly describe the id type, because sometimes mysql fails to recognize the type of id. so you explicitly set as in both tables in your entity class@GeneratedValue (strategy = GenerationType.IDENTITY)

edited Dec 26 '17 at 19:08

ndmeiri

4,979
12
37
45

answered Dec 26 '17 at 18:04

Qaiser Iqbal Moeeni

11

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

26 Answers26

Linked

Related