mysql 8 (innodb) foreign key constraints on newly created indexes-CodePudding

Suppose I have a table items
with columns id (PRIMARY), name(VARCHAR), section_id (BIGINT), updated_at (DATETIME),

and a table sections with id (PRIMARY).

Naturally, items.section_id is a foreign key that refers to sections.id.

Suppose there is an index on items of the columns (section_id, name). I believe that if you tried to drop this index, you would get an error that it is needed in a foreign key constraint. I can accept this.

Now, I want to create a new index, like create index ix_section_id_id_updated_at on items (section_id, id, updated_at). MySQL lets me do this, but if I go to drop this table, I get that same error: it fails, because it is needed in a foreign key constraint.

Why should this be? It already has one index that can be used for this foreign key check. Further, the error does NOT go away with set FOREIGN_KEY_CHECKS=0;. Is there a way to force MySQL to not associate the new index with the foreign key, so that it is quick to drop? This is necessary because I will be running the migration on a production server with temporary downtime, and need to be able to quickly revert the migration in case of anything going wrong afterwards.

CodePudding user response：

I can reproduce your issue if I don't create an index on section_id and allow mysql to do so on the creation of a foreign key(as described in the manual). Adding a new index drops the auto generated key and if you then drop the new index an error is generated because of the requirement to have a key , and mysql does not auto generate one on a drop.. . If you manually generate a key on section_id this problem does not happen..and the newly created compound index drops successfully.

drop table if exists items;
drop table if exists sections;

create table items(id int PRIMARY key, name varchar(3), section_id BIGINT, updated_at DATETIME);

create table sections(id bigint primary key);

alter table items
    add foreign key fk1(section_id) references sections(id);
    
show create table items;

CREATE TABLE `items` (  `id` int(11) NOT NULL,  
`name` varchar(3) DEFAULT NULL,  
`section_id` bigint(20) DEFAULT NULL, 
 `updated_at` datetime DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `fk1` (`section_id`),  
CONSTRAINT `fk1` FOREIGN KEY (`section_id`) REFERENCES `sections` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

alter table items
    add key key1(section_id, name);

show create table items;

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `name` varchar(3) DEFAULT NULL,
  `section_id` bigint(20) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key1` (`section_id`,`name`),
  CONSTRAINT `fk1` FOREIGN KEY (`section_id`) REFERENCES `sections` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and with manually generated key

drop table if exists items;
drop table if exists sections;

create table items(id int PRIMARY key, name varchar(3), section_id BIGINT, updated_at DATETIME);

create table sections(id bigint primary key);


alter table items
    add key sid(section_id);
alter table items
    add foreign key fk1(section_id) references sections(id);
    
show create table items;

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `name` varchar(3) DEFAULT NULL,
  `section_id` bigint(20) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sid` (`section_id`),
  CONSTRAINT `fk1` FOREIGN KEY (`section_id`) REFERENCES `sections` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

alter table items
    add key key1(section_id, name);

show create table items;

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `name` varchar(3) DEFAULT NULL,
  `section_id` bigint(20) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sid` (`section_id`),
  KEY `key1` (`section_id`,`name`),
  CONSTRAINT `fk1` FOREIGN KEY (`section_id`) REFERENCES `sections` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;