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

Answer 1

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.

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

   and/or

2. One of the referenced FKs is not a primary key or an index in the table, both scenarios will produce err 150.

3. 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