Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Given the following tables:

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

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`)

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]> show warnings;

| 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)                                                                                                                       |

Any idea why I get that error? FYI I looked it up and it appears that it was a bug but not anymore.

share|improve this question
up vote 1 down vote accepted

It looks like your data types don't match. In order to create a foriegn key between two tables the data types must be the same. You have comment_id in the longdesc table as INT(11) and comment_id in the trace table as MEDIUMINT(9).

I hope this helps you.

share|improve this answer
Yep that was it. Thanks – Mark D Aug 24 '12 at 18:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.