Pages

31 July, 2023

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

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.

No comments:

Post a Comment

Thanks