Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
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
-
Activity
Can we make the FOREIGN KEY check work without having to create a duplicate secondary index on the same columns?
The fix looks OK, but the added test still adds a duplicated index. I think that it could be avoided.
Also, I think that this had better be stress tested together with MDEV-31441, before we can push this.
origin/10.4-MDEV-29092 6dc31fde449e2cc6fc819cfd72916214ebf71c22 2023-12-04T16:14:46+05:30
performed well in RQG testing. No significant difference to current official 10.4.
Ran into the same issue:
11.0.1 b075191ba8598af6aff5549e6e19f6255aef258a (Optimized)
11.0.1-opt>CREATE TABLE t (c INT KEY) ENGINE=InnoDB;Query OK, 0 rows affected (0.010 sec)11.0.1-opt>ALTER TABLE t ADD FOREIGN KEY(c) REFERENCES t(c);ERROR 1025 (HY000): Error on rename of './test/#sql-alter-24171f-4' to './test/t' (errno: 150 "Foreign key constraint is incorrectly formed")11.0.1 b075191ba8598af6aff5549e6e19f6255aef258a (Optimized)
2023-01-24 17:34:50 4 [ERROR] InnoDB: In ALTER TABLE `test`.`t` has or is referenced in foreign key constraints which are not compatible with the new table definition.This is possibly connected with
MDEV-20480.