Mysql cross-database foreign key

cross-database, foreign-keys, mysql

Solution

Your syntax is not correct, try this

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ext_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ext_id` (`ext_id`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ext_id`) REFERENCES `d1`.`t1` (`int_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Problem

I am trying to create a cross database foreign key. When I run the following code on the same database ``` CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `int_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `int_id` (`int_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ext_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `ext_id` (`ext_id`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ext_id`) REFERENCES `t1` (`int_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ``` However, when create t1 on one databse (d1) and then run the following code on a second databse (d2), I receive the generic error: #1005 - Can't create table 'userdata.t2' (errno: 150) ``` CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ext_id` varchar(128) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `ext_id` (`ext_id`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`ext_id`) REFERENCES `d1.t1` (`int_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ``` Any help will be appreciated. Thanks!

Original source