Why does this MySQL insert fail with foreign key constraint error?

Question

I'm at a loss here. I can't figure out why this insert fails:

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

It fails with:

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)

Here's how the keys are set up:

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      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

And this is the data in the tables:

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)

At first I thought it was because I had primary key constraint in lokacijaSubjekta set to (subjektID, lokacijaID), and also a foreign key from subjektID referencing subjekt.subjektID, so I removed that primary key and added an extra auto_increment column lokacijaSubjektaID to use as primary key instead, but nothing changed.

EDIT: Here are table descriptions, as requested:

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    |                |
+--------------------+------------------+------+-----+---------+----------------+

There are no triggers.

show triggers;
Empty set (0.01 sec)

EDIT 2: I was able to resolve this by dropping the table subjekt and re-creating it along with all the foreign keys, then refilling data. While doing this, I noticed some other tables that referenced subjekt had mismathced signedness on the referenced column, then more tables referencing those tables, and this basically cascaded over most of the tables in the database. When I fixed all the signedness mismatches and restored foreign key constraints, I could insert data to lokacijaSubjekta without further problems.

Answer 1

The error is essentially saying that the value you are supplying for the foreign key column (the second column in your INSERT) '1' is not found in the subjectID column of the subjekt table.

From everything you've shown, it appears that the value is there. MySQL normally interprets a string literal like that in a numeric context, and it should evaluate to integer value 1. It's possible there's some setting in sql_mode that's making MySQL more "strict" than it is by default. Check your setting of sql_mode

SHOW VARIABLES LIKE 'sql_mode'

You could try adding a 0 to the string literal,

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

You could also try that in the INSERT statement, or try removing the single quotes, but I don't think that will make a difference (apart from some strange setting of sql_mode).

You said you already verified there aren't any triggers interfering with the INSERT.

Some other possibilities to consider:

Was the row with subjekID=1 inserted into the subjekt by another session, as part of a transaction, and a COMMIT has not yet been issued? (If you're using InnoDB, do a SHOW INNODB STATUS command to get more information about the last foreign key error.)

Was a previous incarnation of the subjekt table renamed, moved to a different database, and a new subjekt table created? An existing foreign key references "stays" with the table it was referencing, it may be referencing the "old" table in the other schema. You could check the information_schema.constraints table, check the schema of the table, and of the referenced table.

Since subjektID is the PRIMARY KEY, we wouldn't expect that InnoDB index corruption would be an issue.

After checking those things, I'm stumped.

Is it possible that invalid data already exists, data was inserted when FOREIGN_KEY_CHECKS was disabled... and in this session FOREIGN_KEY_CHECKS is enabled, and it's this insert statement that's causing InnoDB to "check" some other rows in the table, and not just the one being inserted?

What storage engine? What version of MySQL?

It's a puzzle.

Answer 2

Possibly because you have put string delimiters around your integer values, transforming them into char values:

... values (NULL, '1', '1');

instead of

... values (NULL, 1, 1);

The Ascii value of the char '1' is inserted instead of the value 1.

Also, if lokacijaSubjektaID is an autoincrement value, you should try to insert a Null value into it. You need to remove this column entirely from your insert statement.

Answer 3

You should use Default or 0 instead of null and if you correct the references sometime you should disconnect your IDE and reconnect it.

Try:

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