CODEWITHSUNDEEP
mysql
Duke
Duke

Reputation: 11

MySQL 1452 Error. I can't see why its not working. Foreign Keys

I just can't seem to figure out what is the problem with my foreign key part of the code? I have looked at other threads and can't seem to get it to work without an error. Any help would be awesome. Thanks.

drop database db5;  
create database if not exists db5;  
use db5;  
create table if not exists bookTable(  
bookNumber integer not null primary key,  
bookName varchar(50),  
bookPrice decimal(5,2),  
coverType varchar(15) default"hardcover",  
publicationDate datetime);  

insert into bookTable
(bookNumber,bookName,bookPrice,coverType,publicationDate)  
values
(100,"Intro to Programming",145.99,"paperback",01/20/2009),
(110,"Networking",135.95,"paperback",02/18/2015);

create table if not exists instructorTable(
instructorNumber integer not null primary key,
instructorName varchar(20),
instructorPhone varchar (10),
instructorOfficeNumber varchar(5));

create table if not exists courseTable(
courseNo integer not null primary key,
courseName varchar(20),
semester varchar(10),
bookNumber integer not null,
instructorNumber integer not null,
foreign key(bookNumber) references bookTable(bookNumber),
foreign key(instructorNumber) references instructorTable(instructorNumber));

insert into courseTable
(courseNo,courseName,semester,bookNumber,instructorNumber)
values
(101,"Programming1","Spring",100,500),
(102,"Intro to Networking","Fall",110,600);

insert into instructorTable
(instructorNumber,instructorName,instructorPhone,instructorOfficeNumber)
values
(500,"Tom Hart",5703235555,237),
(600,"Jackie Knight",5703235566,245);

use db5;
select *
from bookTable, courseTable, instructorTable;

Upvotes: 1

Views: 32

Answers (2)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

You cannot insert this:

insert into courseTable
(courseNo,courseName,semester,bookNumber,instructorNumber)
values
(101,"Programming1","Spring",100,500),
(102,"Intro to Networking","Fall",110,600);

Because you don't have "101","102" in this table instructorTable(instructorNumber) and bookTable(bookNumber);

Primary key table must exists first before you insert the foreign key record into "courseTable";

Upvotes: 1

Federico Fernandez
Federico Fernandez

Reputation: 31

You must run the instructorTable inserts before the courseTable ones

Upvotes: 1

Related Questions