Delete a Primary Key Without Affecting Foreign Key Constrain to Other Table

Question

I want to delete a record from a school table without affecting a foreign key to the department name. I tried but I got this message:

"Cannot delete or update a parent row: a foreign key constraint fails (arusms.department, CONSTRAINT department_ibfk_1 FOREIGN KEY (school_name) REFERENCES school (school_name) ON UPDATE CASCADE)"

Answer 1

I'm not sure why you would want to do that. If you delete the school, the department will be orphaned. That's the point of having foreign keys in the first place, to enforce referential integrity. If you want the department to remain and to be able to do this, you will need to alter the foreign key to include ON DELETE SET NULL. Otherwise, you will have to drop the constraint, perform the delete, and recreate the constraint.

Answer 2

Your error message is hiding the real cause.

(
arusms.department, 

CONSTRAINT department_ibfk_1 
  FOREIGN KEY (school_name) 
  REFERENCES school (school_name) 
  ON UPDATE CASCADE
)

When you created the foreign key constarint, you omitted the ON DELETE part. MySQL used the default action for this, which is ON DELETE RESTRICT. See the MySQL docs: FOREIGN KEY Constraints

If you want to be able to delete schools without cascading effect to the related departments, you can either

• remove the FK constraint or

• make the column (department.school_name) nullable and alter the constraint to have the ON DELETE SET NULL action.

If you want to be able to delete schools and cascading deleting the related departments, you can

• alter the constraint to have the ON DELETE CASCADE action.