2

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!

4
  • You are asking only about InnoDB, correct?
    – Rick James
    Commented Dec 3, 2022 at 20:10
  • What does SHOW CREATE TABLE product_order say?
    – Rick James
    Commented Dec 3, 2022 at 20:31
  • @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
    – leoce
    Commented Dec 4, 2022 at 14:59
  • 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.)
    – Rick James
    Commented Dec 4, 2022 at 18:08

1 Answer 1

2

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.

2
  • 1
    Ditto for 8.0.31.
    – Rick James
    Commented Dec 3, 2022 at 20:18
  • 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.
    – leoce
    Commented Dec 4, 2022 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.