mysql - 跨库外键，1215错误

Question

我尝试创建 db1 表 read_x_tag_0 列外键 db2 列下面是我的代码，不确定我做对了吗？它显示 1215 错误，找不到原因？

数据库'读取'
表'read_x_tag_0'

CREATE TABLE `read_x_tag_0` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `read_id` int(11) unsigned NOT NULL,
  `tag_0_id` int(11) unsigned NOT NULL,
  `tag_0_sequence` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `read_id` (`read_id`),
  KEY `tag_0_id` (`tag_0_id`),
  CONSTRAINT `read_x_tag_0_ibfk_1` FOREIGN KEY (`read_id`) REFERENCES `read` (`id`) ON DELETE CASCADE,
  CONSTRAINT `read_x_tag_0_ibfk_2` FOREIGN KEY (`tag_0_id`) REFERENCES `tag_0`.`tag_0` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

数据库'读取'
表'读'

CREATE TABLE `read` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `intro` text COLLATE utf8_unicode_ci,
  `content` text COLLATE utf8_unicode_ci,
  `sequence` int(11) unsigned NOT NULL,
  `visible` int(11) unsigned NOT NULL COMMENT '1:visible,0:invisible',
  `create_at` datetime NOT NULL,
  `create_by` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `update_at` datetime NOT NULL,
  `update_by` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

数据库“tag_0”
表'tag_0'

CREATE TABLE `tag_0` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `visible` int(11) unsigned NOT NULL COMMENT '1:visible,0:invisible',
  `sequence` int(11) unsigned NOT NULL,
  `create_at` datetime NOT NULL,
  `create_by` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `update_at` datetime NOT NULL,
  `update_by` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `subject` (`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

您不应该将 . 放在反引号内。反引号会转义其中的所有字符，因此它不会被视为数据库和表名之间的分隔符。应该是:

REFERENCES `db2`.`tag_0`

在您的约束中，您为两个约束使用相同的名称 read_x_tag_0。所有约束都必须有不同的名称，因此请重命名其中一个。