MySQL InnoDB foreign key requirements (index/unique/primary)

Question

MySQL 5.6 Doc has set certain conditions and restrictions on foreign keys:

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 14.6.2.1, "Clustered and Secondary Indexes").

NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key. InnoDB does not, which is an extension of standard SQL

I took the code example and made a few changes: removing category from product's primary key, and only referencing category in the product_order table:

CREATE TABLE product (
 category INT NOT NULL,
 id INT NOT NULL,
 price DECIMAL,
 PRIMARY KEY(id)
);
CREATE TABLE customer (
 id INT NOT NULL,
 PRIMARY KEY (id)
);
CREATE TABLE product_order (
 no INT NOT NULL AUTO_INCREMENT,
 product_category INT NOT NULL,
 product_id INT NOT NULL,
 customer_id INT NOT NULL,
 PRIMARY KEY(no),
 FOREIGN KEY (product_category)
 REFERENCES product(category)
 ON UPDATE CASCADE ON DELETE RESTRICT,
 FOREIGN KEY (customer_id)
 REFERENCES customer(id)
);

This code will get different output in different versions of MySQL:

• 5.7.34: ERROR 1215 (HY000): Cannot add foreign key constraint
• 8.0.27: ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'product_order_ibfk_1' in the referenced table 'product'
• 8.0.30: No Error, product_order created

I looked at the release notes but can't pinpoint the changes that make the code functional. MySQL 8.0 Doc has a similar statement that can't explain this:

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 15.6.2.1, "Clustered and Secondary Indexes").

NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key. InnoDB does not, which is an extension of standard SQL.

I wonder if my tests in 3 environments can be replicated. And if so, what is the reason behind 8.0.30's pass on the code? Thanks!

• Rick James: You are asking only about InnoDB, correct?
• Rick James: What does SHOW CREATE TABLE product_order say?
• leoce: @RickJames thank you for the tips! I forgot to check the engine on paiza.io and assumed it is InnoDB for 8.0.30, the code can pass because the default engine is MyISAM
• Rick James: My question was a trick question -- the SHOW would have spelled out the Engine being used. (And perhaps other subtle things that your original CREATE left out.)

Answer 1

8.0.30: No Error, product_order created

I tested on MySQL version 8.0.30 and it fails creating the table product_order.

Below the test:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE product (
 -> category INT NOT NULL,
 -> id INT NOT NULL,
 -> price DECIMAL,
 -> PRIMARY KEY(id)
 -> );
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> CREATE TABLE customer (
 -> id INT NOT NULL,
 -> PRIMARY KEY (id)
 -> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE product_order (
 -> no INT NOT NULL AUTO_INCREMENT,
 -> product_category INT NOT NULL,
 -> product_id INT NOT NULL,
 -> customer_id INT NOT NULL,
 ->
 -> PRIMARY KEY(no),
 ->
 -> FOREIGN KEY (product_category)
 -> REFERENCES product(category)
 -> ON UPDATE CASCADE ON DELETE RESTRICT,
 ->
 -> FOREIGN KEY (customer_id)
 -> REFERENCES customer(id)
 -> );
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'product_order_ibfk_1' in the referenced table 'product'

In this fiddle the table isn't created.

• Rick James: Ditto for 8.0.31.
• leoce: Thank you for the double-check! I've also tested this on db-fiddle.com and it can't complete the creation. Further investigation, thanks to @RickJames revealed that the cloud environment I was using, paiza.io, makes the default engine MyISAM for 8.0.30, and that's why the code can pass.