Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Description
Setting to minor because a foreign key on a column referencing itself doesn't make much sense. But possibly a more meaningful use case can be invented based on this.
For a foreign key to self-reference a column, the table should have two keys on the column, otherwise FK creation fails:
|
10.3 96be3fe8 |
MariaDB [test]> create table t (a int, key(a), foreign key(a) references t(a)) engine=InnoDB; |
ERROR 1005 (HY000): Can't create table `test`.`t` (errno: 150 "Foreign key constraint is incorrectly formed") |
MariaDB [test]> show warnings;
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 150 | Create table `test`.`t` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'foreign key(a) references t(a)) engine=InnoDB'. | |
| Error | 1005 | Can't create table `test`.`t` (errno: 150 "Foreign key constraint is incorrectly formed") | |
| Warning | 1215 | Cannot add foreign key constraint for `t` | |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
3 rows in set (0.000 sec) |
Instead it has to be done like this:
MariaDB [test]> create table t (a int, key(a), key(a), foreign key(a) references t(a)) engine=InnoDB; |
Query OK, 0 rows affected, 1 warning (0.068 sec) |
(the warning is about duplicate keys being deprecated, but it's not important in the context of this issue).
So, we get this structure:
MariaDB [test]> show create table t \G |
*************************** 1. row ***************************
|
Table: t |
Create Table: CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL, |
KEY `a` (`a`), |
KEY `a_2` (`a`), |
CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.001 sec) |
Now we can drop the second key, as long as the algorithm is anything but COPY, the FOREIGN_KEY_CHECKS doesn't prevent it:
MariaDB [test]> select @@foreign_key_checks; |
+----------------------+ |
| @@foreign_key_checks |
|
+----------------------+ |
| 1 |
|
+----------------------+ |
1 row in set (0.000 sec) |
 |
MariaDB [test]> alter table t drop key a_2; |
Query OK, 0 rows affected (0.029 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
And we get this:
MariaDB [test]> show create table t \G |
*************************** 1. row ***************************
|
Table: t |
Create Table: CREATE TABLE `t` ( |
`a` int(11) DEFAULT NULL, |
KEY `a` (`a`), |
CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.000 sec) |
As we already know, it is an invalid structure, it can't be used to re-create the table (not even with FOREIGN_KEY_CHECKS=0, so if it happens to be in a dump, it will be broken):
MariaDB [test]> drop table if exists t; |
Query OK, 0 rows affected (0.033 sec) |
 |
MariaDB [test]> set foreign_key_checks=0; |
Query OK, 0 rows affected (0.000 sec) |
 |
MariaDB [test]> CREATE TABLE `t` ( |
-> `a` int(11) DEFAULT NULL, |
-> KEY `a` (`a`), |
-> CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`a`) |
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
ERROR 1005 (HY000): Can't create table `test`.`t` (errno: 150 "Foreign key constraint is incorrectly formed") |
MariaDB [test]> show warnings;
|
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 150 | Create table `test`.`t` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' FOREIGN KEY (`a`) REFERENCES `t` (`a`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1'. | |
| Error | 1005 | Can't create table `test`.`t` (errno: 150 "Foreign key constraint is incorrectly formed") | |
| Warning | 1215 | Cannot add foreign key constraint for `t` | |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
3 rows in set (0.000 sec) |
Attachments
Issue Links
- relates to
-
MDEV-20480 Obsolete internal parser for FK in InnoDB
-
- Closed
-
-
-
- Confirmed
-