ERROR: Error 1005: Can't create table (errno: 150)

Question

Long time user, first time poster.

I am TRYING to create a database for a university assignment and am getting the above error code (ERROR: Error 1005: Can't create table 'mydb.songs' (errno: 150))

I have checked to see all fk's and Pk's are the same. All tables should also be of the same type.

Can anyone help?

Thanks

Executing SQL script in server ERROR: Error 1005: Can't create table 'mydb.songs' (errno: 150)

CREATE TABLE IF NOT EXISTS `mydb`.`Songs` (
  `SongId` INT NOT NULL,
  `SongTitle` VARCHAR(45) NULL,
  `SongLength` VARCHAR(45) NULL,
  `ProductionDate` VARCHAR(45) NULL,
  `Author` VARCHAR(45) NULL,
  `Price` VARCHAR(45) NULL,
  `AuthorId` INT NULL,
  `Musicians_MusicianId` INT NOT NULL,
  `Instruments_InstrumentId` INT NOT NULL,
  `MediaFiles_MediaId` INT NOT NULL,
  PRIMARY KEY (`SongId`, `Musicians_MusicianId`, `Instruments_InstrumentId`, `MediaFiles_MediaId`),
  INDEX `fk_Songs_Musicians1_idx` (`Musicians_MusicianId` ASC),
  INDEX `fk_Songs_Instruments1_idx` (`Instruments_InstrumentId` ASC),
  INDEX `fk_Songs_MediaFiles1_idx` (`MediaFiles_MediaId` ASC),
  CONSTRAINT `fk_Songs_Musicians1`
    FOREIGN KEY (`Musicians_MusicianId`)
    REFERENCES `mydb`.`Musicians` (`MusicianId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Songs_Instruments1`
    FOREIGN KEY (`Instruments_InstrumentId`)
    REFERENCES `mydb`.`Instruments` (`InstrumentId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Songs_MediaFiles1`
    FOREIGN KEY (`MediaFiles_MediaId`)
    REFERENCES `mydb`.`MediaFiles` (`MediaId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 18 succeeded, 1 failed

Fetching back view definitions in final form. Nothing to fetch

Was there more to that error message? Usually it gives a reason? — Kevin Bowersox, Dec 06 '13 at 18:22
Does the user you are running that script as have proper permissions on `mydb`? — Mike Brant, Dec 06 '13 at 18:23
possible duplicate of [MySQL Errno 150](http://stackoverflow.com/questions/16227199/mysql-errno-150) or http://stackoverflow.com/questions/9018584/error-code-1005-cant-create-table-errno-150?rq=1 — nos, Dec 06 '13 at 18:24
Ive tried adding a unique index. changing the ON DELETE and ON UPDATE attributes, but no change to error. — user3075549, Dec 06 '13 at 18:44
@user3075549 - please provide query to create other tables too.. — Fathah Rehman P, Dec 07 '13 at 08:47
@nos - this is a different issue and solution than those in 9018584. — cerd, Dec 07 '13 at 20:45

cerd · Answer 1 · 2013-12-07T21:02:16.830

From MySQL Docs (Ref):

In the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS.

Using the above without seeing the DDL for your other tables explicity:

The problem is one of the following unless I missed something when replicating what you are trying to do without seeing your other tables.

You have either a data type mismatch in one of the referenced FKs (i.e. INT FK ON CHAR)

and/or
One of the referenced FKs is not a primary key or an index in the table, both scenarios will produce err 150.
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

Reference from MySQL manual

See the below fiddle with working versions of all tables/your statement, to compare your tables DDL with

SQLFiddle with Working DDL

Thanks.I think the problem was that I already had a table of the same name. (would this cause this error) I dropped all other databases and tables and it worked. — user3075549, Dec 07 '13 at 20:57
@user3075549 - No that would not cause the error. If the table exists already, then the query will execute and return 0 rows (success) because `...IF NOT EXISTS` checks if the table exists and if it does will exit gracefully. You likely had #2/#3 as the issue (not index/PK) and solved it by adding those indexes you mentioned adding while in your solution. — cerd, Dec 07 '13 at 21:04

ERROR: Error 1005: Can't create table (errno: 150)

1 Answers1