#1005 – Can’t create table `musicplayer`.`Albums` (errno: 150 “Foreign key constraint is incorrectly formed”)

I’m trying to define some tables with SQL, but when I execute it I have an error. Below there is my code and the error which I receive.

CREATE TABLE Artists 
(
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255)
);

CREATE TABLE Albums 
(
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    releasedate DATE,
    artistid INT,
    genreid INT,
    picture VARCHAR(255),

    CONSTRAINT `fk_albums_artists` 
        FOREIGN KEY (artistid) REFERENCES Artists(id),
    CONSTRAINT `fk_albums_genres` 
        FOREIGN KEY (genreid) REFERENCES Genres(id)
);

CREATE TABLE Tracks
(
    id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    playtime INT NOT NULL,
    albumid INT,

    CONSTRAINT `fk_tracks_album` 
        FOREIGN KEY (albumid) REFERENCES Albums(id)    
);

CREATE table Genres
(
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL
);

Error in my SQL query:

MySQL said: Documentation

1005 – Can’t create table musicplayer.Albums (errno: 150 “Foreign key constraint is incorrectly formed”) (Details…)

Answer

Declare the tables in order, so the tables are defined before they are referenced:

CREATE TABLE Artists (
    id int AUTO_INCREMENT PRIMARY KEY NOT null,
    name varchar(255) not null,
    description varchar(255)
);

CREATE table Genres(
    id int AUTO_INCREMENT PRIMARY KEY NOT null,
    name varchar(255) not null
);


CREATE TABLE Albums (
    id int AUTO_INCREMENT PRIMARY KEY NOT null,
    name varchar(255) not null,
    releasedate date,
    artistid int,
    genreid int,
    picture varchar(255),
    CONSTRAINT `fk_albums_artists` FOREIGN KEY (artistid) REFERENCES Artists(id),
    CONSTRAINT `fk_albums_genres` FOREIGN KEY (genreid) REFERENCES Genres(id)
);


CREATE TABLE Tracks(
    id int(11) AUTO_INCREMENT PRIMARY KEY NOT null,
    name varchar(255) not null,
    playtime int not null,
    albumid int,
    CONSTRAINT `fk_tracks_album` FOREIGN KEY (albumid) REFERENCES Albums(id)    
);

Here is a db<>fiddle.

You can still have tables that reference each other. However, you will need to declare such foreign key constraints using ALTER TABLE rather than in the CREATE TABLE statement. However, you do not need to do this for these table definitions.