CODEWITHSUNDEEP
sql
Ben McCoy
Ben McCoy

Reputation: 11

SQL: "foreign key constraint fails" error message

I get an error when attempting to insert data into the songs table. I'm not sure why.

Any help would be greatly appreciated. Thanks. This is me adding details so it will let me post haha. 

create table artist
(
    id int primary key auto_increment,
    name varchar(128) not null,
    nationality varchar(128)
) ENGINE=InnoDB;

insert into artist (name, nationality) values ('Metallica', 'American');
insert into artist (name, nationality) values ('Rush', 'Canadian');

create table album
(
    id int primary key auto_increment,
    name varchar (128) not null,
    artist int not null,
    foreign key (artist) references artist(id),
    genre int,
    foreign key (genre) references genre(id)
) ENGINE=InnoDB;

insert into album (name, artist, genre) values ('Ride the Lightning', 1, 1);
insert into album (name, artist, genre) values ('Moving Pictures', 2, 2);

create table song
(
    id int primary key auto_increment,
    name varchar (128) not null,
    duration varchar (128),
    album int not null,
    foreign key (album) references album(id)
) ENGINE=InnoDB;

insert into song (name, duration, album) values ('Fade to Black', '1 min', 1);
insert into song (name, duration, album) values ('Tom Sawyer', '2 min', 2);

create table genre
(
    id int primary key auto_increment,
    name varchar (128) not null,
    description varchar (256)
) ENGINE=InnoDB;

insert into genre (name, description) values ('Rock', 'Lots of drums and guitars');
insert into genre (name, description) values ('Metal', 'Drums and guitars on steroids');

Upvotes: 1

Views: 49

Answers (3)

Himanshu
Himanshu

Reputation: 3970

One is the order of the create and insert statements shouldve given you error already as table referres doesnt exists etc.

Assuming if the order is rightly implemented, as per the above error not able to insert data in songs is likely because Your album table has a null id or id which you are inserting in songs doesnt exists in album first insert the data in album table then insert into songs table as the foreign key of songs table is checking the album table for id data

Upvotes: 0

VBoka
VBoka

Reputation: 9083

Here is the correct way to do it: HOW TO DO IT

This is some reasons why it did not work:

There is no table "genre" when you try to create table album:

create table album
(
    id int primary key auto_increment,
    name varchar (128) not null,
    artist int not null,
    foreign key (artist) references artist(id),
    genre int,
    foreign key (genre) references genre(id)
) ENGINE=InnoDB;

Here is the DEMO

Also, when you create table "genre" on time, there is another problem. You have to insert data in table "genre" to be able to insert other data. Here is the DEMO where all works.

Upvotes: 1

QuestionGuyBob
QuestionGuyBob

Reputation: 313

The order of your code is causing the problem. You need to move the 

create table genre
(
    id int primary key auto_increment,
    name varchar (128) not null,
    description varchar (256)
) ENGINE=InnoDB;

insert into genre (name, description) values ('Rock', 'Lots of drums and guitars');
insert into genre (name, description) values ('Metal', 'Drums and guitars on steroids');

Up above the "Create table album"

Upvotes: 1

Related Questions