mysql - 为什么此 MySQL 插入会因外键约束错误而失败？

Question

我在这里不知所措。我不明白为什么这个插入失败:

insert into lokacijaSubjekta (lokacijaSubjektaID, subjektID, lokacijaID) values (NULL, '1', '1');

它失败了:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`nano`.`lokacijaSubjekta`, CONSTRAINT `fk_lokacijaSubjekta_subjekt1` FOREIGN KEY (`subjektID`) REFERENCES `subjekt` (`subjektID`) ON DELETE CASCADE ON UPDATE CASCADE)

key 的设置方式如下:

show index from lokacijaSubjekta;
+------------------+------------+-----------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name                          | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-----------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| lokacijaSubjekta |          0 | PRIMARY                           |            1 | lokacijaSubjektaID | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| lokacijaSubjekta |          1 | fk_lokacijaSubjekta_lokacija1_idx |            1 | lokacijaID         | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| lokacijaSubjekta |          1 | fk_lokacijaSubjekta_subjekt1      |            1 | subjektID          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+-----------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+

show index from subjekt;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| subjekt |          0 | PRIMARY  |            1 | subjektID   | A         |       35603 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

这是表中的数据:

select * from subjekt where subjektID=1;
+-----------+---------------+
| subjektID | subjektPravni |
+-----------+---------------+
|         1 |             1 |
+-----------+---------------+

select * from lokacija where lokacijaID = 1;
+------------+------------------+------------------+
| lokacijaID | geografskaSirina | geografskaDuzina |
+------------+------------------+------------------+
|          1 |             NULL |             NULL |
+------------+------------------+------------------+

select * from lokacijaSubjekta;
Empty set (0.00 sec)

起初我以为是因为我在 lokacijaSubjekta 中设置了主键约束为 (subjektID, lokacijaID)，还有一个来自 subjektID< 的外键 引用 subjekt.subjektID，所以我删除了那个主键并添加了一个额外的 auto_increment 列 lokacijaSubjektaID 用作主键，但是什么都没有改变。

编辑:根据要求，这里是表格描述:

describe subjekt;
+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| subjektID     | int(11) unsigned | NO   | PRI | NULL    |       |
| subjektPravni | tinyint(1)       | NO   |     | 0       |       |
+---------------+------------------+------+-----+---------+-------+

describe lokacija;
+------------------+----------------+------+-----+---------+----------------+
| Field            | Type           | Null | Key | Default | Extra          |
+------------------+----------------+------+-----+---------+----------------+
| lokacijaID       | int(11)        | NO   | PRI | NULL    | auto_increment |
| geografskaSirina | decimal(18,12) | YES  |     | NULL    |                |
| geografskaDuzina | decimal(18,12) | YES  |     | NULL    |                |
+------------------+----------------+------+-----+---------+----------------+

describe lokacijaSubjekta;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| lokacijaSubjektaID | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| subjektID          | int(11) unsigned | NO   | MUL | NULL    |                |
| lokacijaID         | int(11)          | NO   | MUL | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

没有触发器。

show triggers;
Empty set (0.01 sec)

编辑 2:我能够通过删除表 subjekt 并重新创建它以及所有外键，然后重新填充数据来解决这个问题。在执行此操作时，我注意到其他一些引用 subjekt 的表在引用的列上有错误的符号匹配，然后更多表引用这些表，这基本上级联了数据库中的大多数表。当我修复所有符号不匹配并恢复外键约束后，我可以将数据插入 lokacijaSubjekta 而不会出现进一步的问题。

Answer 1

该错误实质上是说您为外键列(INSERT 中的第二列)'1' 提供的值未找到 在 subjekt 表的 subjectID 列中。

从您展示的所有内容来看，似乎都存在值(value)。 MySQL 通常像在数字上下文中那样解释字符串文字，它的计算结果应该是整数值 1。有可能在 sql_mode 中有一些设置使 MySQL 比默认情况下更“严格”。检查您的 sql_mode 设置

SHOW VARIABLES LIKE 'sql_mode'

您可以尝试在字符串文字中添加一个 0，

SELECT '1'+0  and verify it returns integer value of 1.

您也可以在 INSERT 语句中尝试这样做，或者尝试删除单引号，但我认为这不会有什么不同(除了一些奇怪的 sql_mode 设置)。

您说您已经验证没有任何触发器干扰 INSERT。

需要考虑的其他一些可能性:

subjekID=1 的行是否被另一个 session 插入到 subjekt 中，作为事务的一部分，并且 COMMIT 尚未发出？ (如果您使用的是 InnoDB，请执行 SHOW INNODB STATUS 命令以获取有关最后一个外键错误的更多信息。)

subjekt 表的前一个版本是否已重命名、移动到不同的数据库并创建了一个新的 subjekt 表？现有外键引用“保留”在它引用的表中，它可能引用其他模式中的“旧”表。您可以检查 information_schema.constraints 表，检查表的架构以及引用表的架构。

由于 subjektID 是 PRIMARY KEY，我们预计 InnoDB 索引损坏不会成为问题。

在检查了这些东西之后，我被难住了。

是否有可能无效数据已经存在，数据是在 FOREIGN_KEY_CHECKS 被禁用时插入的......并且在这个 session 中 FOREIGN_KEY_CHECKS 被启用，并且它是这个插入语句导致 InnoDB“检查”表中的一些其他行，而不仅仅是被插入的行？

什么存储引擎？什么版本的 MySQL？

这是一个难题。