MySQL外键约束错误(150)求助：外键约束创建失败排查

Hey there! Let's break down why you're hitting that 150 foreign key constraint incorrectly formed error — it's a common gotcha with MySQL foreign keys, but easy to fix once you know the rule.

The Root Cause #

MySQL requires that the column you're referencing in a foreign key is either a PRIMARY KEY or has a UNIQUE index on it. In your Bands table, band_name is just a NOT NULL column with no uniqueness constraint. Since multiple bands could theoretically have the same name (or you might accidentally duplicate it later), MySQL can't guarantee that the foreign key in Albums points to a single, unambiguous row in Bands — hence the error.

Two Fixes to Choose From #

Fix 1: Add a Unique Constraint to band_name in Bands

If you're confident band names will always be unique, you can add a UNIQUE constraint to band_name so MySQL allows the foreign key:

CREATE TABLE Bands(
    band_ID int NOT NULL AUTO_INCREMENT,
    band_name varchar(20) NOT NULL UNIQUE, -- Add UNIQUE constraint here
    band_year int NOT NULL,
    PRIMARY KEY (band_ID)
);

CREATE TABLE Albums(
    album_ID int NOT NULL AUTO_INCREMENT,
    album_name varchar(20) NOT NULL,
    band_name varchar(20) NOT NULL,
    PRIMARY KEY (album_ID),
    FOREIGN KEY (band_name) REFERENCES Bands(band_name)
);

Fix 2: Reference the band_ID Primary Key (Recommended)

This is the more robust approach for database design. Primary keys are meant to be the stable, unique identifier for rows, and using band_ID as the foreign key avoids issues if a band ever changes its name (you won't have to update every related album row):

CREATE TABLE Bands(
    band_ID int NOT NULL AUTO_INCREMENT,
    band_name varchar(20) NOT NULL,
    band_year int NOT NULL,
    PRIMARY KEY (band_ID)
);

CREATE TABLE Albums(
    album_ID int NOT NULL AUTO_INCREMENT,
    album_name varchar(20) NOT NULL,
    band_ID int NOT NULL, -- Use band_ID instead of band_name
    PRIMARY KEY (album_ID),
    FOREIGN KEY (band_ID) REFERENCES Bands(band_ID)
);

Quick Note #

I'd strongly recommend Fix 2 because it follows relational database best practices — primary keys are designed to stay consistent, while string fields like names can change. This will save you headaches down the line if you need to modify band data!

内容的提问来源于stack exchange，提问作者Aaron Smyth Richardson