Mysql 8.4 Causes DDL Errors for Northwind

[EdwardOst]

Mysql InnoDB was previously more lenient than most sql engines regarding foreign constraints as described here https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html .

In Mysql 8.4 that was fixed by introducing new restrictions on fk constraints. This causes the northwind.sql to throw the error below.

ERROR 6125 (HY000) at line 177: Failed to add the foreign key constraint. Missing unique key for constraint 'OrderDetail_ibfk_1' in the referenced table 'SalesOrder'

This can be avoided by setting the restrict_fk_on_non_standard_key server variable to OFF, but this workaround is deprecated and will be supposedly removed in a future release.

[trevorkennedy]

$ cd db-samples/mysql
$ mysql --host=localhost --port=3306 --user=root --password=admin --protocol=TCP --database sys
mysql> SET restrict_fk_on_non_standard_key = OFF;
mysql> source northwind.sql