mysql - #1005 - 无法创建表 `musicplayer` .`Albums`(错误号 : 150 "Foreign key constraint is incorrectly formed")

Question

我试图用 SQL 定义一些表，但是当我执行它时出现错误。下面是我的代码和我收到的错误。

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

我的 SQL 查询出错:

MySQL said: Documentation

1005 - Can't create table musicplayer.Albums (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)

Answer 1

按顺序声明表，以便在引用表之前定义表:

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是一个数据库<> fiddle 。

您仍然可以拥有相互引用的表。但是，您需要使用 ALTER TABLE 而不是在 CREATE TABLE 语句中声明此类外键约束。但是，您不需要对这些表定义执行此操作。