Bug #89570 Dropping of parent key for foreign key is allowed
Submitted: 7 Feb 2018 12:45 Modified: 23 Apr 2018 13:45
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.5-debug OS:Any
Assigned to: CPU Architecture:Any

[7 Feb 2018 12:45] Dmitry Lenev
Description:
It is allowed to drop primary key which serves as parent key in a foreign key.

This seems to be regression from InnoDB changes done as part of fix for bug
#25915132 "INPLACE ALTER TABLE WITH FOREIGN KEYS CAUSES TABLE DEFINITION MISMATCH".

Before these changes attempt to drop such primary key caused an error from SE.
Now this error is not emitted in this case. According to Jimmy such checks should
happen on SQL-layer nowadays.

How to repeat:
CREATE TABLE t2 (
  fkfrom1 CHAR(32) NOT NULL PRIMARY KEY
);
CREATE TABLE t1 (
  fkto2 CHAR(32) NOT NULL PRIMARY KEY,
  FOREIGN KEY (fkto2) REFERENCES t2 (fkfrom1)
);

# The below statement should error out, but it does not.
ALTER TABLE t2 DROP PRIMARY KEY;
[23 Apr 2018 13:45] Paul DuBois
Posted by developer:
 
Fixed in 8.0.12.

It was possible to drop a primary key that served as the parent key
in a foreign key relationship.