Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.2
-
None
-
None
Description
ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS creates index on the given column using the key id provided but that name is not the same as constraint name (at least on InnoDB). That makes this feature unusable (and some cases to crash).
Problem is that MySQL does not really know foreign key constraint names, it knows key names. Thus
alter table t2 add constraint f foreign key if not exists (i) references t1(pk) |
Would create foreign key constraint f on InnoDB data dictionary and MySQL key f on MySQL data dictionary. But
alter table t2 add foreign key if not exists f(i) references t1(pk); |
Would create foreign key constraint t2_ibfk_1 on InnoDB data dictionary and MySQL key f on MySQL data dictionary. In this case you can't do:
alter table t2 drop foreign key if exists t2_ibfk_1; |
Because, there is no key t2_ibfk_1 on MySQL data dictionary, and
alter table t2 drop foreign key if exists f; |
would not also work because there is no foreign key constraint f on InnoDB data dictionary while there is a key f on MySQL. There is no real test cases on mysql-test suite for adding/dropping foreign keys with if [not] exists feature.
Test case:
create table t1 (pk int primary key) engine=InnoDB; |
create table t2 (i int) engine=InnoDB; |
alter table t2 add foreign key if not exists f(i) references t1(pk); |
show create table t2; |
alter table t2 drop foreign key if exists f; |
show warnings;
|
alter table t2 drop foreign key if exists t2_ibfk_1; |
show warnings;
|
show create table t2; |
drop table t2,t1; |
create table t1 (pk int primary key) engine=InnoDB; |
create table t2 (i int) engine=InnoDB; |
alter table t2 add constraint f foreign key if not exists (i) references t1(pk); |
show create table t2; |
alter table t2 drop foreign key if exists f; |
show warnings;
|
show create table t2; |
alter table t2 add constraint f foreign key if not exists (i) references t1(pk); |
show create table t2; |
alter table t2 add constraint f foreign key if not exists (i) references t1(pk); |
show warnings;
|
show create table t2; |
drop table t2,t1; |
Attachments
Issue Links
- relates to
-
MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE (MWL #252)
-
- Closed
-