How do you get a double-column unique key as foreign key?

Question

I have the following two tables in MariaDB 11.0.2:

CREATE TABLE `Languages` (
  `Name` char(49) DEFAULT NULL,
  `ISO_639_1` char(2) NOT NULL,
  `Language_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Main_Flag` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Language_ID`),
  UNIQUE KEY `Languages_UN` (`ISO_369_1`,`Main_Flag`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and

CREATE TABLE `Tests` (
  `Test_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Test_Name` varchar(50) DEFAULT NULL,
  `ISO_639_1` char(2) NOT NULL,
  `Main_Flag` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Test_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

but I cannot assign the double column unique-key to their counterparts:

MariaDB [my_db]> ALTER TABLE  Tests ADD CONSTRAINT Test_Language_FK FOREIGN KEY (ISO_639_1, Main_Flag) REFERENCES Languages(ISO_639_1, Main_Flag);
ERROR 1005 (HY000): Can't create table `my_db`.`Tests` (errno: 150 "Foreign key constraint is incorrectly formed")

There are no anomalies in the index of the Test table:

MariaDB [my_db]> show index from Tests;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Tests |          0 | PRIMARY  |            1 | Test_ID     | A         |          37 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0,001 sec)

What is holding me back? The data types are the same and the collation and charsets are also a match.

Answer 1

The error is a bit cryptic, you can get some more info by using

SHOW ENGINE INNODB STATUS

In the LATEST FOREIGN KEY ERROR section you'll see constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns

You require and index

CREATE INDEX MYINDEX ON Languages (ISO_639_1, Main_Flag);

See this working fiddle

Answer 2

The best with inno den is to run the last comand, so that you try much the information about the error.

In your you are missing a unique or primary key in the referenced table for the column combination

CREATE TABLE `Languages` (
  `Name` char(49) DEFAULT NULL,
  `ISO_639_1` char(2) NOT NULL,
  `Language_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Main_Flag` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Language_ID`),
  UNIQUE KEY `unique_language` (`ISO_639_1`,`Main_Flag`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `Tests` (
  `Test_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Test_Name` varchar(50) DEFAULT NULL,
  `ISO_639_1` char(2) NOT NULL,
  `Main_Flag` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Test_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE  Tests 
ADD CONSTRAINT
  Test_Language_FK FOREIGN KEY (ISO_639_1, Main_Flag)
  REFERENCES Languages(ISO_639_1, Main_Flag); 

Records: 0  Duplicates: 0  Warnings: 0

show engine innodb status

Type	Name	Status
InnoDB		===================================== 2023-07-29 20:36:04 0x7f8018926700 INNODB MONITOR OUTPUT

fiddle