CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keyssql-delete
Xiaoren
Xiaoren

Reputation: 29

Deleting row from a table with foreign key constraints

le wi

Hi there, I'm fairly new to sql and I'm stuck with deleting rows from flight261 table which has foreign keys Pilot ID, Aircraft ID and Airlines ID. Whenever I try to delete a row, I always get

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (flightdb.staff261, CONSTRAINT staff261_ibfk_1 FOREIGN KEY (Flight_ID261) REFERENCES Flight261 (Flight_ID261)).

Any help would be appreciated!

Upvotes: 1

Views: 4800

Answers (1)

Chris Johnson
Chris Johnson

Reputation: 21956

When you create a foreign key, a rule is created that defines how parent table records relate to child table records. By default, the rule is ON DELETE RESTRICT, which means you can’t delete the parent if there are children. But you can choose to use other rules: ON DELETE CASCADE means deleting the parent will ripple-delete the children too, and ON DELETE SET NULL means deleting the parent will set the corresponding field (that references the parent) to null in the child records.

Which rule to use depends on what you’re trying to do. RESTRICT is the “safe” rule but the others have their uses. CASCADE makes sense when the child is part of the parent, i.e. if you delete an invoice it only makes sense to delete the line items of the invoice too. SET NULL makes sense when the relationship is optional in the first place.

However there’s a bigger issue with your data model. The way you’ve modeled the staff for the flight says each flight can have multiple staff (good) but also that each staff member can only be assigned one flight at a time (bad). You should add one more table. Your Staff table should just be a list of staff and their attributes without any relation to Flights. Then add a flight-to-staff junction table (maybe “FlightStaff”?) that has an FK to Flight, an FK to Staff, and that is all. A “junction” table of this kind is the usual way to model a many-to-many relation.

Then on that table, think about DELETE rules. Between Flight and FlightStaff, use RESTRICT if you want to prevent someone from deleting a flight without unassigning all the staff first — maybe they need an email to know not to show up for that flight; or use CASCADE to delete it all at once. Between Staff and FlightStaff, use RESTRICT if you want to make sure someone doesn’t delete a staff member (maybe the person just quit?) without realizing they have one or more flights assigned that need to be assigned to someone else; or use CASCADE to delete it all at once.

Upvotes: 2

Related Questions