MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE main (
  main_id INT PRIMARY KEY
  );
SHOW CREATE TABLE main;
CREATE TABLE slave (
  slave_id INT PRIMARY KEY,
  main_id INT,
  FOREIGN KEY (main_id) REFERENCES main (main_id) 
  );
SHOW CREATE TABLE slave;


Table
Create Table


main
CREATE TABLE `main` (
  `main_id` int(11) NOT NULL,
  PRIMARY KEY (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1




Table
Create Table


slave
CREATE TABLE `slave` (
  `slave_id` int(11) NOT NULL,
  `main_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`slave_id`),
  KEY `main_id` (`main_id`),
  CONSTRAINT `slave_ibfk_1` FOREIGN KEY (`main_id`) REFERENCES `main` (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE INDEX idx ON slave (main_id, slave_id);
SHOW CREATE TABLE slave; -- индекс KEY `main_id` (`main_id`) удалён

Records: 0  Duplicates: 0  Warnings: 0




Table
Create Table


slave
CREATE TABLE `slave` (
  `slave_id` int(11) NOT NULL,
  `main_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`slave_id`),
  KEY `idx` (`main_id`,`slave_id`),
  CONSTRAINT `slave_ibfk_1` FOREIGN KEY (`main_id`) REFERENCES `main` (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


DROP TABLE slave;
CREATE TABLE slave (
  slave_id INT PRIMARY KEY,
  main_id INT,
  KEY main_id (main_id),
  FOREIGN KEY (main_id) REFERENCES main (main_id)
  );
SHOW CREATE TABLE slave;


Table
Create Table


slave
CREATE TABLE `slave` (
  `slave_id` int(11) NOT NULL,
  `main_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`slave_id`),
  KEY `main_id` (`main_id`),
  CONSTRAINT `slave_ibfk_1` FOREIGN KEY (`main_id`) REFERENCES `main` (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE INDEX idx ON slave (main_id, slave_id);
SHOW CREATE TABLE slave; -- индекс KEY `main_id` (`main_id`) НЕ удалён

Records: 0  Duplicates: 0  Warnings: 0




Table
Create Table


slave
CREATE TABLE `slave` (
  `slave_id` int(11) NOT NULL,
  `main_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`slave_id`),
  KEY `main_id` (`main_id`),
  KEY `idx` (`main_id`,`slave_id`),
  CONSTRAINT `slave_ibfk_1` FOREIGN KEY (`main_id`) REFERENCES `main` (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1