CODEWITHSUNDEEP
mysqlsqldatabase
Ali Usman
Ali Usman

Reputation: 61

Which gets deleted first? Primary or Foreign key?

When we delete a primary key which is foreign key in other table, which gets deleted first? Primary key in first table or Foreign key in the other table? I was asked this question in interview. Please provide some reason with answer too :)

Upvotes: 5

Views: 6474

Answers (3)

HLGEM
HLGEM

Reputation: 96648

The PK record cannot be deleted until the FK records are gone. That is part of the very definition of what having such relationships is and one of the main reasons for having a FK relationship. The reason is that you don't want to have orphaned child records that no longer have a parent and thus do not make sense. This is the data integrity issue.

Databases will give an error if you try to delete the PK without first deleting the child records. Cascade delete can hide this by deleting them first in the background, but this is very bad thing much of the time and should be avoided. You do not want to willy nilly delete child records, there are many times when the existence of a child record is telling you that the parent should not be deleted.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271211

In general, you would need to delete the foreign key references before deleting the primary key. Otherwise, the foreign key constraint would be invalid.

I might guess that this is the answer the interviewer is looking for.

In practice, though, the answer would be "at the same time". The normal way to accomplish this is using a cascading delete foreign key reference. The deletes would all take place in the same transaction (on most databases at least), so they would not take effect until the commit.

If you were doing this manually, you do typically do:

  • Drop the foreign key constraint.
  • Re-set the values in the columns for the foreign key reference (typically to NULL).
  • Delete the appropriate row(s) in the primary key table.

Upvotes: 6

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Well to me looks like a tricky question.

My answer would be neither, you will get an error unless you define a DELETE CASCADE constraint

In that case row reference that PK would be delete first.

Upvotes: 1

Related Questions