MySQL Bugs: #91721: Error adding unique key

Bug #91721	Error adding unique key
Submitted:	19 Jul 2018 21:09	Modified:	30 Oct 2018 13:37
Reporter:	Ryan Brothers	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.6, 5.5.60, 5.6.40, 5.7.22,8.0.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am running into an issue where adding a unique key on a table causes an error.  It looks like MySQL is trying to drop a key as part of adding the new key.  I am running into this error in MySQL 5.6, but it also exists in 5.7 and 8.0.

Running the below SQL returns an error on the "ALTER TABLE table2" line:

ERROR 1553 (HY000) at line 26: Cannot drop index 'table1_id': needed in a foreign key constraint

The error seems to be occurring because the index on table1_id in table2 was auto-created via the foreign key back to table1.

Can you please check why the "ALTER TABLE table2" is failing in this scenario?

If the index on table1_id is explicitly declared in the CREATE TABLE for table2 as "KEY (table_id1)", then the "ALTER TABLE table2" succeeds.

How to repeat:
DROP TABLE IF EXISTS table3;
DROP TABLE IF EXISTS table2;
DROP TABLE IF EXISTS table1;

CREATE TABLE table1 (
  table1_id int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (table1_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE table2 (
  table2_id int NOT NULL AUTO_INCREMENT,
  table1_id int NOT NULL,
  table2_value varchar(255) DEFAULT NULL,
  PRIMARY KEY (table2_id),
  CONSTRAINT FOREIGN KEY (table1_id) REFERENCES table1 (table1_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE table3 (
  table3_id int NOT NULL AUTO_INCREMENT,
  table1_id int NOT NULL,
  table2_id int NOT NULL,
  PRIMARY KEY (table3_id),
  CONSTRAINT FOREIGN KEY (table1_id, table2_id) REFERENCES table2 (table1_id, table2_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE table2
  ADD UNIQUE KEY (table1_id, table2_value);

Hello!

Thank you for the report and test case.

Thanks,
Umesh

In a table, internally the primary key is appended to the secondary key. This part of the secondary key is hidden from the user.

In the referencing table, there must be a key where the foreign key columns are listed as the first columns in the same order. Such a key is created on the referencing table automatically if it does not exist. This key is internally appended by the primary key but hidden from the user. Till 5.7, InnoDB considers hidden part of this secondary key when determining if it can be a parent key. Since the key and order matches, we ignore the fact that it is a hidden key and allow the foreign key to be created. But the fact that this is a hidden key is taken into consideration when doing Inplace Alter and it rejects the alter.

The fix would be to disallow the table creation if composite keys are not specified explicitly, which has been done in 8.0. We thought about backporting but further analysis confirmed that fix would cause change of behavior in earlier GAs which could affect some existing user applications and hence deferred.

A simple workaround:
. Create parent key explicitly (i.e make hidden part of the key visible)

For eg:
CREATE TABLE t1 (
t1_id1 INT NOT NULL, PRIMARY KEY(t1_id1)
) ENGINE= InnoDB;

CREATE TABLE t2 (
t2_id1 INT, t2_id2 INT, PRIMARY KEY (t2_id1), KEY a(t2_id2, t2_id1),
CONSTRAINT FOREIGN KEY (t2_id2) REFERENCES t1 (t1_id1)
) ENGINE= InnoDB;

CREATE TABLE t3 (
t3_id1 INT NOT NULL, t3_id2 INT NOT NULL,
CONSTRAINT FOREIGN KEY (t3_id1, t3_id2) REFERENCES t2 (t2_id2, t2_id1)
) ENGINE= InnoDB;

ALTER TABLE t2 ADD UNIQUE KEY (t2_id2);