Serj.by Serj.by - 2 months ago 7
MySQL Question

Foreign keys work occasionally in MySQL

I am quite new to StackOverflow so please sorry in the case I am doing something wrong. And sorry for my English - it's not my native language.

Ok, make long story short. I am using:

Server version: 5.6.31 MySQL Community Server (GPL)


There I have two tables like this:

Table texts

CREATE TABLE `texts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`body` text,
`source_id` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `texts_sources_key` (`source_id`),
CONSTRAINT `texts_sources_key` FOREIGN KEY (`source_id`) REFERENCES `texts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8


table texts_sources

CREATE TABLE `texts_sources` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`short_desc` varchar(255) DEFAULT NULL,
`long_desc` text,
`url` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8


As you can see - there is foreign key linking these tables with ON DELETE SET NULL ON UPDATE CASCADE.

Here is initial data:

mysql> select * from texts;
+----+------+-----------+
| id | body | source_id |
+----+------+-----------+
| 1 | *** | 1 |
+----+------+-----------+
1 row in set (0.00 sec)

mysql> select * from texts_sources;
+----+------------+-----------+----------------+
| id | short_desc | long_desc | url |
+----+------------+-----------+----------------+
| 1 | * | NULL | http://url.com |
+----+------------+-----------+----------------+
1 row in set (0.00 sec)


Now let's do some magic.

mysql> update texts_sources set id=5;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from texts_sources;
+----+------------+-----------+----------------+
| id | short_desc | long_desc | url |
+----+------------+-----------+----------------+
| 5 | * | NULL | http://url.com |
+----+------------+-----------+----------------+
1 row in set (0.01 sec)

mysql> select * from texts;
+----+------+-----------+
| id | body | source_id |
+----+------+-----------+
| 1 | *** | 1 |
+----+------+-----------+
1 row in set (0.00 sec)


Not working. Some more fun:

mysql> delete from texts_sources;
Query OK, 1 row affected (0.18 sec)

mysql> select * from texts;
+----+------+-----------+
| id | body | source_id |
+----+------+-----------+
| 1 | *** | 1 |
+----+------+-----------+
1 row in set (0.00 sec)


Not working. Ok. Maybe it is not working at all. Let's then try this way:

mysql> update texts set source_id=5;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`serj_by`.`texts`, CONSTRAINT `humor_sources_key` FOREIGN KEY (`source_id`) REFERENCES `texts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)
mysql> insert into texts (body, source_id) values ("***", 7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`serj_by`.`texts`, CONSTRAINT `humor_sources_key` FOREIGN KEY (`source_id`) REFERENCES `texts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)


Working. Fun. Even funnier (texts_sources is empty, hope you remember):

mysql> insert into texts (body, source_id) values ("***", 1);
Query OK, 1 row affected (0.16 sec)

mysql> select * from texts_sources;
Empty set (0.00 sec)

mysql> select * from texts;
+----+------+-----------+
| id | body | source_id |
+----+------+-----------+
| 1 | *** | 1 |
| 2 | *** | 1 |
+----+------+-----------+
2 rows in set (0.00 sec)


So my question is obvious. What it was? And how make it working in all cases as expected? Any help is highly appreciated. Thank you in advance!

Answer

Let's take a close look at this:

CONSTRAINT `texts_sources_key` 
  FOREIGN KEY (`source_id`) 
    REFERENCES `texts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

Though you have named this as texts_sources_key the foreign key actually references a column in the same table. You probably intended it to be

CONSTRAINT `texts_sources_key` 
  FOREIGN KEY (`source_id`) 
    REFERENCES `texts_sources` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

additionally you would need to create the text_sources table first before you make that constraint.

Comments