SQLSTATE [23000]: Integrity constraint violation: 1452 Unable to add or update child row: foreign key constraint is not fulfilled

Question

I am trying to insert values ​​into my comment table and I am getting an error. His saying is that I cannot add or update a child row, and I have no idea what that means. My diagram 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'm 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 am getting is as follows:

SQLSTATE [23000]: Violation of integrity constraint: 1452 Unable to add or update child row: foreign key constraint is not fulfilled ( koxu1996_test . user , CONSTRAINT user_ibfk_1 FOREIGN KEY ( country ) LINKS country_type ( id ) ON DELETE NO ACTIONS TURNED user_ibfk_1

Answer 1

It just means that the value for the country column in the table comments you insert does not exist in the country_type table or you do not insert the value for the country in the user table. Keep in mind that the column values ​​in the table comments depend on the identifier values ​​in the country_type table.

Answer 2

You have foreign keys between this table and another table, and this new row will violate this restriction.

You should be able to see the restriction, if you run show create table user , it displays as CONSTRAINT... and it shows which columns refer to which tables / columns.

In this case, country refers to country_type (id) , and you do not specify the value of country . You need to put the value that exists in country_type .

Answer 3

Just to throw my own problem, if it can help someone else, I copied / pasted entries into my migration files and messed up by putting quotes around the integer. Since the value I was trying to enter was considered a string passing into an integer field that referred to another integer, this error occurred.

Answer 4

Another option might be that your primary key in the source table is NOT unsigned, so I solved the same insertion with (notification 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;

Answer 5

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

Answer 6

In my case, the link values ​​do not match in the corresponding table. Just make sure that the values ​​exist in the referenced table, and that the current rows have corresponding valid values.