Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.5, 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11
Description
When an `ALTER TABLE` statement tries to drop an constraint while also adding one, the drop operation is not performed:
|
|
MariaDB [test]> create table a (id int primary key); |
Query OK, 0 rows affected (0.016 sec) |
|
|
MariaDB [test]> create table b (id int primary key references a(id)); |
Query OK, 0 rows affected (0.015 sec) |
|
|
MariaDB [test]> show create table b\G |
*************************** 1. row ***************************
|
Table: b |
Create Table: CREATE TABLE `b` ( |
`id` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> alter table b drop constraint b_ibfk_1, add constraint other foreign key (id) references a(id) on update cascade; |
Query OK, 0 rows affected (0.023 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table b\G |
*************************** 1. row ***************************
|
Table: b |
Create Table: CREATE TABLE `b` ( |
`id` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`), |
CONSTRAINT `other` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ON UPDATE CASCADE |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
1 row in set (0.010 sec) |
Attachments
Issue Links
- relates to
-
MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
When an `ALTER TABLE` statement tries to drop an constraint while also adding one, the drop operation is not performed:
``` MariaDB [test]> create table a (id int primary key); Query OK, 0 rows affected (0.016 sec) MariaDB [test]> create table b (id int primary key references a(id)); Query OK, 0 rows affected (0.015 sec) MariaDB [test]> show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.001 sec) MariaDB [test]> alter table b drop constraint b_ibfk_1, add constraint other foreign key (id) references a(id) on update cascade; Query OK, 0 rows affected (0.023 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`), CONSTRAINT `other` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.010 sec) ``` |
When an `ALTER TABLE` statement tries to drop an constraint while also adding one, the drop operation is not performed:
{code:sql} MariaDB [test]> create table a (id int primary key); Query OK, 0 rows affected (0.016 sec) MariaDB [test]> create table b (id int primary key references a(id)); Query OK, 0 rows affected (0.015 sec) MariaDB [test]> show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.001 sec) MariaDB [test]> alter table b drop constraint b_ibfk_1, add constraint other foreign key (id) references a(id) on update cascade; Query OK, 0 rows affected (0.023 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`), CONSTRAINT `other` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.010 sec) {code} |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.9 [ 26905 ] | |
| Fix Version/s | 10.10 [ 27530 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.0 [ 28320 ] |
| Affects Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.9 [ 26905 ] | |
| Affects Version/s | 10.10 [ 27530 ] | |
| Affects Version/s | 10.11 [ 27614 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Sergei Golubchik [ serg ] |
| Assignee | Sergei Golubchik [ serg ] | Marko Mäkelä [ marko ] |
| Link | This issue relates to MDEV-16356 [ MDEV-16356 ] |
| Component/s | Data Definition - Alter Table [ 10114 ] | |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Labels | foreign-keys |
| Assignee | Marko Mäkelä [ marko ] | Vladislav Lesin [ vlad.lesin ] |
| Fix Version/s | 10.9 [ 26905 ] |
| Fix Version/s | 10.10 [ 27530 ] |
| Fix Version/s | 11.0 [ 28320 ] |
| Fix Version/s | 10.4 [ 22408 ] |