主页 / dba / 问题 / 23092
Accepted
Mark D
Asked: 2012-08-25 06:58:59 +0800 CST

外键约束失败

  • 772

给定下表：

CREATE TABLE `longdescs` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` mediumint(9) NOT NULL,
  `who` mediumint(9) NOT NULL,
  `bug_when` datetime NOT NULL,
  `work_time` decimal(7,2) NOT NULL DEFAULT '0.00',
  `thetext` mediumtext NOT NULL,
  `isprivate` tinyint(4) NOT NULL DEFAULT '0',
  `already_wrapped` tinyint(4) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `extra_data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `longdescs_bug_id_idx` (`bug_id`),
  KEY `longdescs_who_idx` (`who`,`bug_id`),
  KEY `longdescs_bug_when_idx` (`bug_when`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `fk_longdescs_bug_id_bugs_bug_id` FOREIGN KEY (`bug_id`) REFERENCES `bugs` (`bug_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_longdescs_who_profiles_userid` FOREIGN KEY (`who`) REFERENCES `profiles` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=82727 DEFAULT CHARSET=utf8

CREATE TABLE `trace` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `comment_id` mediumint(9) NOT NULL,
  `type` varchar(255) NOT NULL,
  `short_hash` char(22) DEFAULT NULL,
  `stack_hash` char(22) DEFAULT NULL,
  `trace_text` mediumtext NOT NULL,
  `quality` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `trace_comment_id_idx` (`comment_id`),
  KEY `trace_short_hash_idx` (`short_hash`),
  KEY `trace_stack_hash_idx` (`stack_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

ALTER TABLE trace ADD CONSTRAINT `fk_trace_comment_id_longdescs_comment_id` FOREIGN KEY (`comment_id`) REFERENCES `longdescs`(`comment_id`) ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)

MariaDB [bugs4]> 显示警告；

| Level  | Code | Message |
| Warning |  150 | Create table 'bugs4/#sql-4bb_6f1d' with foreign key     constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
 |
| Error   | 1005 | Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)                                                                                                                       |

知道为什么我会收到这个错误吗？仅供参考，我查了一下，它似乎是一个错误，但现在不是了。

http://bugs.mysql.com/bug.php?id=16290

mysql

1 个回答

  1. Best Answer

    看起来您的数据类型不匹配。为了在两个表之间创建外键，数据类型必须相同。您在 longdesc 表中的 comment_id 为 INT(11)，在跟踪表中的 comment_id 为 MEDIUMINT(9)。

    我希望这可以帮助你。

    • 1

相关问题