-1

I am new to SQL and trying to create a schema as per the below. However, I can't seem to get the Foreign KEy for the Member table right. I tried adding it after the table was created and that seemed to work initially but now when I try to add a test row of data I get this error.

I'm sure it's something really stupid that I've missed but if anyone could point out what i've done wrong that would be great!

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails
(memberproject.member, CONSTRAINT member_ibfk_2 FOREIGN KEY (MemNo) REFERENCES booking (MemNo))

CREATE DATABASE MemberProject;  

USE MemberProject;  

CREATE TABLE Skill(  
SkillCode VARCHAR(20) NOT NULL,   
Description VARCHAR(140),  
ChargeOutRate VARCHAR(20),  
PRIMARY KEY (SkillCode)  
);  
CREATE TABLE Member(  
MemNo VARCHAR(10) NOT NULL,   
MemName VARCHAR(40),  
Department VARCHAR(20),  
SkillCode VARCHAR(20),  
PRIMARY KEY (MemNo),  
FOREIGN KEY (SkillCode) REFERENCES Skill (SkillCode)  

);  

CREATE TABLE Booking(  
MemNo VARCHAR(10) NOT NULL,   
ProjectNo VARCHAR(10) NOT NULL,   
DateWorkedOn VARCHAR(20),  
PRIMARY KEY (ProjectNo, MemNo),  
FOREIGN KEY (MemNo) REFERENCES Member (MemNo)   
);

could not create db with Member foreign key so am adding it after the fact 

ALTER table Member  
ADD foreign key (MemNo) references Booking (MemNo);  

CREATE TABLE Project(   
ProjectNo VARCHAR(10) NOT NULL,   
ProjName VARCHAR(40),  
StartDate VARCHAR(20),  
EndDate VARCHAR(40),  
Budget VARCHAR(40),  
Proj_Manager_MemNo VARCHAR(10),  
PRIMARY KEY (ProjectNo),  
FOREIGN KEY (ProjectNo) REFERENCES Booking (ProjectNo) ON UPDATE CASCADE  
);
Menelaos
  • 23,508
  • 18
  • 90
  • 155
louised88
  • 1
  • Looks like you are inserting into `Booking` a row with data in `MemNo`, which does not have a parent in `member` – T.S. Feb 03 '20 at 15:37

1 Answers1

0

What data are you inserting? If you are adding data to the member table, you must first have data in the skill table for the skillcode. Same thing for the other tables with foreign keys.

FloridaDBA
  • 77
  • 5