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