MySQL SQLSTATE[23000]：完整性约束违规：1452 无法添加或更新子行：外键约束失败

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

提问by andrzej1_1

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:

我试图做的 mysql 语句看起来像这样：

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_1FOREIGN KEY (country) REFERENCES country_type(id) ON DELETE NO ACTION ON UPDATE NO ACTION)

SQLSTATE[23000]：完整性约束违规：1452 无法添加或更新子行：外键约束失败（koxu1996_test. user, CONSTRAINT user_ibfk_1FOREIGN KEY ( country) REFERENCES country_type( id) ON DELETE NO ACTION ON UPDATE NO ACTION）

回答by Tsimtsum

It just simply means that the value for column country on table comments you are inserting doesn't exist on table country_typeor 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.

这只是意味着您插入的表注释上列country 的值在表country_type上不存在，或者您没有在表user上插入 country 值。请记住，表 comment上列country 的值取决于表country_type上 ID 的值。

回答by Andreas Wederbrand

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.

如果您运行show create table user，您应该能够看到约束，它显示为CONSTRAINT...并显示哪些列引用了哪些表/列。

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

在这种情况下，country引用country_type (id)并且您没有指定 的值country。您需要放置一个存在于country_type.

回答by Ben

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.

只是为了解决我自己的问题，以防它可以帮助其他人，我在迁移文件中复制/粘贴条目，并通过在整数周围加上引号而搞砸了。由于我尝试输入的值被认为是进入引用另一个整数的整数字段的字符串，因此出现了此错误。

回答by Lubomir Andrisek

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

另一种选择可能是您在源表中的主键未签名，因此我使用（注意idint(8) unsigned）解决了相同的插入问题：

CREATE TABLE IF NOT EXISTS user( idint(8) unsigned NOT NULL AUTO_INCREMENT, usernamevarchar(32) COLLATE utf8_bin NOT NULL,
passwordvarchar(64) COLLATE utf8_bin NOT NULL, password_realchar(32) COLLATE utf8_bin NOT NULL, emailvarchar(32) COLLATE utf8_bin NOT NULL, codechar(8) COLLATE utf8_bin NOT NULL,
activatedenum('0','1') COLLATE utf8_bin NOT NULL DEFAULT '0',
activation_keychar(32) COLLATE utf8_bin NOT NULL, reset_keyvarchar(32) COLLATE utf8_bin NOT NULL, namevarchar(32) COLLATE utf8_bin NOT NULL, streetvarchar(32) COLLATE utf8_bin NOT NULL,
house_numbervarchar(32) COLLATE utf8_bin NOT NULL,
apartment_numbervarchar(32) COLLATE utf8_bin NOT NULL, cityvarchar(32) COLLATE utf8_bin NOT NULL, zip_codevarchar(32) COLLATE utf8_bin NOT NULL, phone_numbervarchar(16) COLLATE utf8_bin NOT NULL, countryint(8) NOT NULL, provinceint(8) NOT NULL, peselvarchar(32) COLLATE utf8_bin NOT NULL,
register_timetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
authorised_timedatetime NOT NULL, edit_timedatetime NOT NULL, saldodecimal(9,2) NOT NULL, referer_idint(8) NOT NULL,
levelint(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 ;

CREATE TABLE IF NOT EXISTS user( idint(8) unsigned NOT NULL AUTO_INCREMENT, usernamevarchar(32) COLLATE utf8_bin NOT NULL,
passwordvarchar(64) COLLATE utf8_bin NOT NULL, password_realchar(32) COLLATE utf8_bin NOT NULL, emailvarchar(32) COLLATE NOT NULL , codechar(8) COLLATE utf8_bin NOT NULL,
activatedenum('0','1') COLLATE utf8_bin NOT NULL DEFAULT '0',
activation_keychar(32) COLLATE utf8_bin NOT NULL, reset_keyvarchar(32) COLLATE utf8_bin NOT NULL, namevarchar(32) ) COLLATE utf8_bin NOT NULL, streetvarchar(32) COLLATE utf8_bin NOT NULL,
house_numbervarchar(32) COLLATE utf8_bin NOT NULL,
apartment_numbervarchar(32) COLLATE utf8_bin NOT NULL, cityvarchar(32) COLLATE utf8_bin NOT NULL, zip_codevarchar(32) COLLATE utf8_bin NOT NULL, phone_numbervarchar(16) COLLATE utf8_bin NOT NULL, countryint(8) NOT NULL, provinceint(8) NOT NULL, peselvarchar(32) COLLATE utf8_bin NOT NULL,
register_timetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
authorised_timedatetime NOT NULL , edit_timedatetime NOT NULL, saldodecimal(9,2) NOT NULL, referer_idint(8) NOT NULL,
levelint(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 ;

回答by R T

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

在我的例子中，目标表列的值是空的，而引用表列有它。因此抛出这个错误。

回答by David L

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.

在我的情况下，引用值在相关表上不对应。只需确保引用表中存在值，并且当前行具有相应的有效值。

回答by Bahman

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

就我而言，它在SQL命令中没有问题，但输入不是作为 $request 发送的，所以在 php.ini 文件中：

max_input_vars was 1000 by default and I changed it to :

max_input_vars 默认为 1000，我将其更改为：

max_input_vars = 2000

then you have to restart web server .

那么你必须重新启动网络服务器。