MySQL Foreign Keys Issue

Question

I have 3 table: CD, Song and Song_Details which is a relationship between CD & Song:

create table Song(
    ID int not null auto_increment,
    Title varchar(255) not null,
    Length float not null,
    primary key (ID, Title)
);

create table CD(
    Title varchar(255) not null,
    CD_Number int not null,
    primary key (Title, CD_Number)
);

Create table Song_Details(
    CD_Title varchar(255) not null,
    Song_Title varchar(255) not null,
    Track_Number int not null,
    primary key(CD_Title, Song_Title),
    foreign key(CD_Title) references CD(Title),
    foreign key(Song_Title) references Song(Title)
);

I have managed to find out that this line in Song_Details:

foreign key(Song_Title) references Song(Title) is throwing the Error 1215(HY000): Cannot add foreign key constraint;

Could anyone help me see based on my table, what could be causing this issue?

Read the answers in http://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint for troubleshooting tips. — Bill Karwin, Nov 23 '16 at 23:57
Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. — barudo, Nov 24 '16 at 00:03

score 0 · Accepted Answer · answered Nov 24 '16 at 00:03

Two things. The auto_increment key would normally be the foreign key. Second, you need to make your reference to all the keys defined as the primary or unique key for the table (I don't advise making foreign key references to non-unique keys although MySQL does all that).

So:

create table Song (
    Song_ID int not null auto_increment,
    Title varchar(255) not null,
    Length float not null,
    primary key (ID),
    unique (title)
);


create table CD (
    CD_Id int auto_increment primary key,
    Title varchar(255) not null,
    CD_Number int not null,
    unique (Title, CD_Number)
);


Create table Song_Details(
    CD_ID varchar(255) not null,
    Song_Id varchar(255) not null,
    Track_Number int not null,
    primary key(CD_ID, Song_ID),
    foreign key(CD_ID) references CD(CD_ID),
    foreign key(Song_ID) references Song(Song_ID)
);

Notes:

Use the primary key relationships for the foreign key definitions.
I like to have the primary keys include the table name. That way, the primary key can have the same name as the corresponding foreign keys.
Don't put the titles in more than one place. They belong in the entity tables. Autoincremented ids can then be used to access the titles.

@Drew . . . If they are not unique, then remove the constraint. That is all the more reason to use another column as the id for each row. — Gordon Linoff, Nov 24 '16 at 02:43

MySQL Foreign Keys Issue

1 Answers1