Mysql外键错误150

Question

CREATE TABLE IF NOT EXISTS `questions` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `createddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateddate` timestamp NULL DEFAULT NULL,
  `active_flag` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`question_id`),
  UNIQUE KEY `id_UNIQUE` (`question_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE `alarts` (
  `alart_id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
  `alart_name` varchar(45) NOT NULL,
  `interval` int(10) unsigned NOT NULL,
  `alart_sent_counter` int(10) unsigned NOT NULL,
  `alart_types_id` BIGINT(20) unsigned NOT NULL,
  `contact_group_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`alart_id`),
  FOREIGN KEY (`alart_types_id`) REFERENCES alart_types(`alart_types_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

我想像这样用两个 FOREIGN KEY 创建新表:

CREATE TABLE `alart_question_mapping` (
`alart_question_mapping_id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,  
`question_id` int(11) NOT NULL,
`alart_id` BIGINT(20) unsigned NOT NULL,
PRIMARY KEY (`alart_question_mapping_id`),
FOREIGN KEY (`question_id`) REFERENCES questions(`question_id`),
FOREIGN KEY (`alart_id`) REFERENCES alart_types(`alart_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

但我收到错误: 错误代码:1005。无法创建表“alart_question_mapping”(错误号:150)

我怎样才能创建这个表？

谢谢。

Answer 1

更改语句:

FOREIGN KEY (`alart_id`) REFERENCES alart_types(`alart_id`)

到

FOREIGN KEY (`alart_id`) REFERENCES alarts(`alart_id`)