MySQL Bugs: #46337: Inconsistent MySQL behavior when creating a table with self-ref. FKs

Bug #46337	Inconsistent MySQL behavior when creating a table with self-ref. FKs
Submitted:	22 Jul 2009 10:39	Modified:	12 Apr 2019 18:27
Reporter:	Vladimir Kolesnikov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL will create referencing index for you but will not create referenced one so you will end-up with a table that you cannot insert into unless you manually create the index. This error cannot be repeated with InnoDB as it reports an InnoDB-specific error, but can be repeated with PBXT. It is MySQL that generates underlying indexes "on-the-fly" so I assume this is MySQL bug.

How to repeat:
mysql> create table t1 (c1 int, c2 int, constraint fk1 foreign key (c1) references t1 (c2)) engine = innodb;
ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 150)

mysql> create table t1 (c1 int, c2 int, constraint fk1 foreign key (c1) references t1 (c2)) engine = pbxt;
Query OK, 0 rows affected (0.27 sec)

mysql>  show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `fk1` (`c1`),
  CONSTRAINT `fk1` FOREIGN KEY (`c1`) REFERENCES `test`.`t1` (`c2`)
) ENGINE=PBXT DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into t1 values (1,1);
ERROR 1297 (HY000): Got temporary error -1 'Matching index required for '`t1` (c2)'' from PBXT

Suggested fix:
Either create the corresponding index or don't allow table creation.

Thank you for the report.

Verified as described.

Posted by developer:
 
Hello!

After bug #27506922 "DROPPING OF PARENT KEY FOR FOREIGN KEY IS ALLOWED"
has been fixed in MySQL Server 8.0.12, SQL-layer checks that there is
existing parent key for the foreign key and reports an error if it is
missing. For example:

create table t1 (c1 int, c2 int, constraint fk1 foreign key (c1) references t1 (c2))
# Fails with ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'

So Oracle's and 3rd party storage engines can rely on this check present.
Thus I am closing this bug as fixed in 8.0.12.

Moving this bug to Documenting state to let Documentation team to decide
if anything needs to be added to release notes.

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. In addition, for table creation, the
SQL layer now checks whether there is a parent key for the foreign
key and reports an error if it is missing.