Mysql Foreign key (150 foreign key constraint incorrectly formed )

Question

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_name varchar(20) NOT NULL,
    PRIMARY KEY (album_ID),
    FOREIGN KEY (band_name) REFERENCES Bands(band_name)
);

Can someone please help me with my MySQL statement? I thought a first it was conflicting types buts that's not the case. I'm not sure what's causing the error

GMB · Accepted Answer · 2019-02-27T10:19:04.480

You need the column being referrred by the constraint either to be the primary key of the referred table (best practice) or at least to have an index.

From the documentation:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.

Bottom line : why not store (and reference) the band id in the albums table instead of the band name? It seems to be like this would better respond to your use case.

Mysql Foreign key (150 foreign key constraint incorrectly formed )

1 Answers1