SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

Question

I am trying to insert values into my comments table and I am getting a error. Its saying that I can not add or update child row and I have no idea what that means. My schema looks something like this:

--
-- Baza danych: `koxu1996_test`
--

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8_bin NOT NULL,
  `password` varchar(64) COLLATE utf8_bin NOT NULL,
  `password_real` char(32) COLLATE utf8_bin NOT NULL,
  `email` varchar(32) COLLATE utf8_bin NOT NULL,
  `code` char(8) COLLATE utf8_bin NOT NULL,
  `activated` enum('0','1') COLLATE utf8_bin NOT NULL DEFAULT '0',
  `activation_key` char(32) COLLATE utf8_bin NOT NULL,
  `reset_key` varchar(32) COLLATE utf8_bin NOT NULL,
  `name` varchar(32) COLLATE utf8_bin NOT NULL,
  `street` varchar(32) COLLATE utf8_bin NOT NULL,
  `house_number` varchar(32) COLLATE utf8_bin NOT NULL,
  `apartment_number` varchar(32) COLLATE utf8_bin NOT NULL,
  `city` varchar(32) COLLATE utf8_bin NOT NULL,
  `zip_code` varchar(32) COLLATE utf8_bin NOT NULL,
  `phone_number` varchar(16) COLLATE utf8_bin NOT NULL,
  `country` int(8) NOT NULL,
  `province` int(8) NOT NULL,
  `pesel` varchar(32) COLLATE utf8_bin NOT NULL,
  `register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `authorised_time` datetime NOT NULL,
  `edit_time` datetime NOT NULL,
  `saldo` decimal(9,2) NOT NULL,
  `referer_id` int(8) NOT NULL,
  `level` int(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `country` (`country`),
  KEY `province` (`province`),
  KEY `referer_id` (`referer_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=83 ;

and the mysql statement I am trying to do looks something like this:

INSERT INTO `user` (`password`, `code`, `activation_key`, `reset_key`, `register_time`,                `edit_time`, `saldo`, `referer_id`, `level`) VALUES (:yp0, :yp1, :yp2, :yp3, NOW(), NOW(), :yp4, :yp5, :yp6). Bound with :yp0='fa1269ea0d8c8723b5734305e48f7d46', :yp1='F154', :yp2='adc53c85bb2982e4b719470d3c247973', :yp3='', :yp4='0', :yp5=0, :yp6=1

the error I get looks like this:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (koxu1996_test.user, CONSTRAINT user_ibfk_1 FOREIGN KEY (country) REFERENCES country_type (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

Did you look at any of the similar question/answers here of SO before posting? Your error message clearly tells you that the `koxu1996_test.country` value you are(are not) inserting is not a valid value from `country_type.id`. — Sean, Jul 15 '13 at 07:01

score 33 · Accepted Answer · edited Jun 29 '16 at 11:46

33

It just simply means that the value for column country on table comments you are inserting doesn't exist on table country_type or you are not inserting value for country on table user. Bear in mind that the values of column country on table comments is dependent on the values of ID on table country_type.

edited Jun 29 '16 at 11:46

Bacon Bits

30,782
5
59
66

answered Jul 15 '13 at 06:59

Tsimtsum

981
9
27

score 6 · Answer 2 · answered Jul 15 '13 at 07:02

You have foreign keys between this table and another table and that new row would violate that constraint.

You should be able to see the constraint if you run show create table user, it shows up as CONSTRAINT... and it shows what columns reference what tables/columns.

In this case country references country_type (id) and you are not specifying the value of country. You need to put a value that exists in country_type.

score 2 · Answer 3 · answered Jun 09 '16 at 17:02

Just to throw in my own issue in case it can help someone else, I was copy/pasting entries in my migration files and messed up by putting quotes around an integer. Since the value I was trying to enter was considered a string going into an integer field that was referencing another integer, this error came up.

score 1 · Answer 4 · answered Jun 29 '16 at 11:43

Another option could be thath your primary key in source table IS NOT unsigned, so I solved same insert with (notice id int(8) unsigned):

CREATE TABLE IF NOT EXISTS user ( id int(8) unsigned NOT NULL AUTO_INCREMENT, username varchar(32) COLLATE utf8_bin NOT NULL,
password varchar(64) COLLATE utf8_bin NOT NULL, password_real char(32) COLLATE utf8_bin NOT NULL, email varchar(32) COLLATE utf8_bin NOT NULL, code char(8) COLLATE utf8_bin NOT NULL,
activated enum('0','1') COLLATE utf8_bin NOT NULL DEFAULT '0',
activation_key char(32) COLLATE utf8_bin NOT NULL, reset_key varchar(32) COLLATE utf8_bin NOT NULL, name varchar(32) COLLATE utf8_bin NOT NULL, street varchar(32) COLLATE utf8_bin NOT NULL,
house_number varchar(32) COLLATE utf8_bin NOT NULL,
apartment_number varchar(32) COLLATE utf8_bin NOT NULL, city varchar(32) COLLATE utf8_bin NOT NULL, zip_code varchar(32) COLLATE utf8_bin NOT NULL, phone_number varchar(16) COLLATE utf8_bin NOT NULL, country int(8) NOT NULL, province int(8) NOT NULL, pesel varchar(32) COLLATE utf8_bin NOT NULL,
register_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
authorised_time datetime NOT NULL, edit_time datetime NOT NULL, saldo decimal(9,2) NOT NULL, referer_id int(8) NOT NULL,
level int(8) NOT NULL, PRIMARY KEY (id), KEY country (country), KEY province (province), KEY referer_id (referer_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=83 ;

score 1 · Answer 5 · answered Aug 01 '16 at 04:22

1

In my case, the value was empty for target table column while the reference table column has it. hence was throwing this error.

answered Aug 01 '16 at 04:22

R T

4,369
3
38
49

score 1 · Answer 6 · answered Jan 10 '17 at 21:15

1

In my case the references values does not corresponding on the related table. Just be sure the values exist on reference table and currents rows has corresponding valid values.

answered Jan 10 '17 at 21:15

David L

703
6
22

Ali · Answer 7 · 2020-02-29T13:14:12.290

1

In my case , its no problem in SQL commands , but the inputs was not sending as $request , so in php.ini file :

max_input_vars was 1000 by default and I changed it to :

max_input_vars = 2000

then you have to restart web server .

edited Feb 29 '20 at 13:14

answered Feb 28 '20 at 19:03

Ali

47
5

1

Why minus score ? , this is a **Real Solution** and really solved my problem . Some errors are side effect of another errors or incorrect configurations. – Ali Feb 29 '20 at 13:15

score 0 · Answer 8 · answered Mar 05 '22 at 18:39

0

You should pass NULL for empty values. Not empty string such as ''

answered Mar 05 '22 at 18:39

Maduka Jayalath

1,338
1
14
15

Naqi Ch · Answer 9 · 2022-09-01T13:24:22.937

in my case this was the error: Illuminate\Database\QueryException SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'brand_name_ur' cannot be null (SQL: insert into brands (brand_name_en, brand_name_ur, brand_slug_en, brand_slug_ur, brand_image) values (SAMI, ?, sami, سامی, upload/brand/1742772943246486.png))

and fixed by

'brand_name_ur' => $request -> brand_image_ur, < changing brand_image_ur to brand_name_ur. Noob mistak. took my hour.

bcag2 · Answer 10 · 2022-09-19T10:13:18.433

thanks to @Tsimtsum
By running a request like :

SELECT user.id as user_id, country as user_country_type_id, country_type.id as country_type_id
FROM `user`
LEFT JOIN country_type ON country_type.id = user.country
WHERE country_type.id is null;

it probably returns some rows and it should not !

You can delete it (probably a bad idea if you are in production) using request like :

DELETE user
FROM `user`
LEFT JOIN country_type ON country_type.id = user.country
WHERE country_type.id is null;

and adding constraint should be done after !

score 0 · Answer 11 · answered Feb 14 '23 at 20:33

0

add to your migrations

$this->addSql('SET foreign_key_checks = 0');
.....
$this->addSql('SET foreign_key_checks = 1');

answered Feb 14 '23 at 20:33

Saoussene Toubal

1
2

1

please add more details to your answer, it helps to understand the solution better – Ruben Feb 19 '23 at 05:02

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

11 Answers11

Linked