Question

I am trying to delete these tables from my data base and I keep getting:

"mysql> DROP TABLE Courses;

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

I am using the command DROP TABLE Courses; Below is my sql file

use sdev300;

// Create a student table
CREATE TABLE Students (
PSUsername varchar(30) primary key,
FirstName varchar(30),
LastName varchar(30),
EMail varchar(60)
);
CREATE TABLE Courses(
CourseID int primary key,
CourseDisc varchar(4),
CourseNum varchar(4),
CourseTitle varchar(75)
);
CREATE TABLE StudentCourses (
StudentCourseID int primary key,
CourseID int,
PSUsername varchar(30),
Constraint SC1 Foreign Key (CourseID) references Courses(CourseID) on delete cascade,
Constraint SC2 Foreign Key (PSUsername) references Students(PSUsername)
On delete cascade
);

Answer 1

Question:

I am trying to delete these tables from my data base and I keep getting:

"mysql> DROP TABLE Courses;

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

I am using the command DROP TABLE Courses; Below is my sql file

use sdev300;

// Create a student table
CREATE TABLE Students (
PSUsername varchar(30) primary key,
FirstName varchar(30),
LastName varchar(30),
EMail varchar(60)
);
CREATE TABLE Courses(
CourseID int primary key,
CourseDisc varchar(4),
CourseNum varchar(4),
CourseTitle varchar(75)
);
CREATE TABLE StudentCourses (
StudentCourseID int primary key,
CourseID int,
PSUsername varchar(30),
Constraint SC1 Foreign Key (CourseID) references Courses(CourseID) on delete cascade,
Constraint SC2 Foreign Key (PSUsername) references Students(PSUsername)
On delete cascade
);

Answer:

As per the problem Statement

SQL Operation to Drop TABLE Courses fails with Error 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This Error #1217 happens when other tables has Foreign Table Key Constraints to the Table that you are trying to delete.

In this Problem Statement.

Column CourseID is Primary Key Constraint to Table Courses

Column CourseID is added as a Foreign Key Constraint to Table StudentCourses

Above Foreign Key Constraint caused this Error #1217 .

This Error #1217 can be avoided by using below SQL Query Approach:

Approach 1:

Use 'FOREIGN_KEY_CHECK' variable to 0 First

Then Do DROP Operation for Table Courses

Then Reset the 'FOREIGN_KEY_CHECK' variable to 1

Above Solution can be achieved using below SQL Query :

SQL> SET FOREIGN_KEY_CHECK=0; DROP TABLE Courses; SET FOREIGN_KEY_CHECK=1;

Approach 2:

Use ALTER SQL Command to do Alter Operation in Table Courses to drop Foreign Key Constraint THEN

Removing the Foreign Key Constraint Column in Table Courses THEN Do DROP TABLE Courses;

Above Solution can be achieved using below SQL Query :

SQL> ALTER TABLE Courses DROP FOREIGN KEY CourseID; DROP TABLE Courses;

=======================================

Both Approach will do the Drop Operation, But Approach 1 is Ideal Solution.