1

When I try to create a new table, I get an error:

Error Code: 1005. Can't create table `db'.'db_timesheet_check' (errno: 150)

The problem I think is to create the foreign keys. Without the foreign keys it works, but when I try to add the keys I got the error.

What am I doing wrong?

DROP TABLE IF EXISTS `db`.`db_check_causes`;

CREATE  TABLE IF NOT EXISTS `db`.`db_check_causes` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `code` VARCHAR(8) NOT NULL ,
  `info` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `code_UNIQUE` (`code` ASC) );

DROP TABLE IF EXISTS `db`.`db_timesheet_check`;

CREATE  TABLE IF NOT EXISTS `db`.`db_timesheet_check` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `TSId` INT(10) UNSIGNED NOT NULL ,
  `causeId` INT(10) UNSIGNED NOT NULL ,
  `checked` BIT NOT NULL DEFAULT 0 ,
  `checkedBy` INT(10) UNSIGNED NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `TSId` (`TSId` ASC) ,
  INDEX `causeId` (`causeId` ASC) ,
  INDEX `checkedBy` (`checkedBy` ASC) ,
  CONSTRAINT `TSId`
    FOREIGN KEY (`TSId` )
    REFERENCES `db`.`db_timesheet` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `causeId`
    FOREIGN KEY (`causeId` )
    REFERENCES `db`.`db_check_causes` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `checkedBy`
    FOREIGN KEY (`checkedBy` )
    REFERENCES `db`.`db_user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Zunrise
  • 11
  • 1
  • Does the table `db_timesheet` exist? – hjpotter92 Sep 10 '13 at 07:11
  • Lots of duplicates looking at the related questions in the sidebar: http://stackoverflow.com/questions/3128115/mysql-error-1005-hy000-cant-create-table-receitascakephp-recipes-errno?rq=1 http://stackoverflow.com/questions/8999537/resolving-error-code-1005-cant-create-table-errno-150-error?rq=1 http://stackoverflow.com/questions/9018584/error-code-1005-cant-create-table-errno-150?rq=1 http://stackoverflow.com/questions/9503334/mysql-error-1005-cant-create-table-mytable-errno-150?rq=1 http://stackoverflow.com/questions/13674164/mysql-5-1-1005-cant-create-table-datacode-foto-errno-150-error?rq=1 – Carlos Campderrós Sep 10 '13 at 07:12
  • yes, the table does exists – Zunrise Sep 10 '13 at 07:13
  • 1
    Most probable cause: a referenced field in a foreign key does not have the same exact definition as the table field – Carlos Campderrós Sep 10 '13 at 07:13
  • Can you provide `CREATE TABLE` code for `db_timesheet` and `db_user`? – hjpotter92 Sep 10 '13 at 07:18
  • I solved the problem. The database default engine was set to MyISAM. When I converted to InnoDB it work fine. – Zunrise Sep 10 '13 at 08:10

0 Answers0