mysql - 错误代码: 1215.无法在MySQL中添加外键约束

Question

我收到一个错误代码，错误代码:1215。无法在 MySQL 中添加外键约束。我的代码是

DROP TABLE IF EXISTS Formed;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Band;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Track;

CREATE TABLE Formed(
    FormedID int NOT NULL, 
    YearFormed int, 
    CountryFormed varchar(50), 
    CityFormed varchar(50), 
    BandMembers varchar(400), 
    PRIMARY KEY(FormedID))
    ENGINE=InnoDB;

CREATE TABLE Track (
    TrackID int NOT NULL, 
    AlbumID int NOT NULL,
    Songs varchar (100), 
    TrackNumber varchar (20), 
    Title varchar (20), 
    TrackDuration varchar (4), 
    PRIMARY KEY (TrackID), 
    FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)ON DELETE SET NULL ON UPDATE CASCADE)
    ENGINE=InnoDB;

CREATE TABLE Album(
    AlbumID int NOT NULL,
    TrackID int NOT NULL,
    BandID int NOT NULL,
    Price varchar(5), 
    PublicationDate varchar(11), 
    Title varchar(30), 
    Genre varchar (36),
    PRIMARY KEY(AlbumID),
    FOREIGN KEY (TrackID) REFERENCES Track(TrackID)ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (BandID) REFERENCES Band(BandID)ON DELETE SET NULL ON UPDATE CASCADE)
    ENGINE=InnoDB;

CREATE TABLE Band(
    BandID int NOT NULL, 
    AlbumID int NOT NULL, 
    RecordLabel varchar(50), 
    PRIMARY KEY(BandID),
    FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)ON DELETE SET NULL ON UPDATE CASCADE)
    ENGINE=InnoDB;

CREATE TABLE Customers (
    CustomerID int NOT NULL, 
    CName varchar (20), 
    CPhone int (11), 
    CEmail varchar (50), 
    CPPaid varchar (50), 
    CPDate date, 
    PRIMARY KEY (CustomerID))
    ENGINE=InnoDB;

它创建了第一个名为Formed的表，但是当它尝试创建第二个表时它给了我错误，我不知道为什么。我对 MySQL 了解一点，并且正在自学外键。我在网上查看了为什么会出现此错误，但找不到任何有用的信息。

Answer 1

从错误消息来看，您似乎正在尝试在 Album 表之前创建 Track 表，因此，它失败了，因为它无法找到父表(和列)用于外键约束。

我建议仅使用主键创建表，然后在创建所有 3 个表后应用外键约束，例如:

ALTER TABLE `Track` 
ADD FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)ON DELETE SET NULL ON UPDATE CASCADE);