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.
0 comments:
Post a Comment
Thanks