CODEWITHSUNDEEP
mysqlsql-deletealter
Randy Gilman
Randy Gilman

Reputation: 457

Getting a ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

I have used the following code before to get what I wanted but then I added some Foreign Keys and it is messing it up:

DELETE FROM Faculty WHERE LastName LIKE 'Z%'

DELETE FROM Courses WHERE FirstYrOffered='1999'

Simply I am trying to delete an entry from the table but it is not working correctly. This is how my tables are looking:

CREATE TABLE Faculty (
FacultyID int,
FirstName varchar(30),
LastName varchar(30),
EMail varchar(60),
BirthDate DATE,
Numbr_Courses int,
PRIMARY KEY (FacultyID)
);

CREATE TABLE Courses(
CourseID int,
CourseDisc varchar(4),
CourseNum varchar(4),
NumbrCred varchar(1),
FirstYrOffered int,
CourseTitle varchar(75),
PRIMARY KEY (CourseID)
);

CREATE TABLE Faculty_Courses(
InstanceID int,
FacultyID int,
CourseDisc varchar(4),
CourseID int,
CourseNum varchar(4),
CourseTitle varchar(75),
PRIMARY KEY (InstanceID),
FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

I have looked up the ALTER TABLE code examples but don't really understand what I am doing with that. Any help would be appreciated.

Upvotes: 0

Views: 4705

Answers (1)

Shadow
Shadow

Reputation: 34231

First you have to delete the corresponding records from Faculty_Courses, then delete the records from faculty and courses tables. Alternatively, set on delete cascade option on the foreign keys.

Upvotes: 2

Related Questions