3

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 :)

Ali Usman
  • 41
  • 1
  • 3
  • What have you found in your search for the answer to this? – Tom H Jan 13 '16 at 15:26
  • 1
    Did the question include SQL in general or was it specifically about MySQL? If it was about MySQL then the correct answer is "it depends on storage engine used". – Mjh Jan 13 '16 at 15:33
  • 1
    Assuming that it refers to `ON DELETE CASCADE`, my response in an interview would be that in an Atomic transaction it is irrelevant, and to spend time finding out the inner workings of the database engine in question is a waste of time. If it was specifically about manually removing records where the FK referential trigger action is `SET NULL` or `RESTRICT` then the answer is you **must** delete the child records first (either because the delete of the parent will fail, or in the case of `SET NULL` with a nullable FK column you lose the ability to identify the children). – GarethD Jan 13 '16 at 15:48
  • @Mjh, would you elaborate on "it depends on storage engine used" ? – Kuba Wyrostek Jan 13 '16 at 19:46
  • 1
    @KubaWyrostek - `MyISAM` doesn't support foreign key constraints and it's not ACID compliant engine. In that case, foreign keys do nothing and only the pk record will get deleted. Therefore, it has to be specified which engine is used because it can be a trick question in case of `MyISAM` – Mjh Jan 14 '16 at 08:42
  • 1
    I wonder what the context of the question is. E.g. to what (level of) job did you apply? There _might_ be a slight difference between the "Primus inter pares in the High Council of MySQL tweakers and liason to the sql99-group" and "Hey, code monkey, you have two minutes. What's in your cookbook?" ;-) Was there something like "isolation level" or "deferrable" surrounding this question? – VolkerK Jan 14 '16 at 09:55
  • Interesting that no one cared to ask: Is it about **deleting** the rows or about **dropping** the constraints (keys)? However - In either case without more information the answer would be: "It depends". So I vote to close this question as "unclear". But reading the answers and comments, I might also choose "opinion based". – Paul Spiegel Sep 26 '19 at 08:39

3 Answers3

6

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.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I agree, plus I would add a "restore the foreign key constraint" after deletion of the rows in the primary key table. – Cynical Jan 13 '16 at 15:35
  • @Cynical That wont work, In this sample Gordon make those rows orphans changing FK to `NULL`. If you want keep the constraint you have delete the FK row and then the PK row. – Juan Carlos Oropeza Jan 13 '16 at 15:37
  • In general I agree with you. Howvwer, the word "cascade", and the language of the mysql documentation on cascaded delete implies that the parent record is removed first, then the corresponding child rows are removed. I believe if the cascaded deletes are not enabled, then you cannot delete the parent row first, even if you are using transactions. – Shadow Jan 13 '16 at 15:38
  • @JuanCarlosOropeza Yeah, you're right! I skipped that bit of the answer :) – Cynical Jan 13 '16 at 15:40
  • @Gordon I like the part of `"at the same time"` forgot the steps go in a transaction. But `the foreign key constraint would be invalid.` isnt correct, the whole point of the constraint is keep the data valid and db wont let you delete the PK in a first place. – Juan Carlos Oropeza Jan 13 '16 at 16:04
  • @JuanCarlosOropeza . . . In case English is not your first language, the use of `would" in this context is the equivalent of the subjunctive in other languages -- in other words, the "would" implies a hypothetical that is not necessarily true. English is an complicated language, and "would" is sometimes used as equivalent to "will", but that is not the intention in this case. – Gordon Linoff Jan 14 '16 at 02:32
1

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.

Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Delete cascade is irrelevant to the question. It is a poor practice most of the time as well. – HLGEM Jan 13 '16 at 15:29
  • 1
    I think the `ON DELETE` is the whole point of the question: depending on how it has been set, the user will be able to delete the referenced entries (therefore foreign keys are deleted first, followed by the primary key), leave them as they are (just the primary key is deleted) or prevent deletion at all, which would result in no elements deleted. – Cynical Jan 13 '16 at 15:33
  • @HLGEM sorry Im confused what you mean is bad practice? and how isnt relevant `DELETE CASCADE`? if you try delete a PK as describe in the question you will get an error. – Juan Carlos Oropeza Jan 13 '16 at 15:34
  • @Cynical That is why I say is a tricky question. I would ask interviwer for more information, how is set the DELETE, do you want delete the foreign key rows or keep it as gordon did. – Juan Carlos Oropeza Jan 13 '16 at 15:42
  • It is a poor practice because it can tie up a database. Yes you get an error, but you can just as easily prevent that by deleting the fks first. And you can do that in loops if need be to prevent locking your database while millions of child records are deleted. – HLGEM Jan 13 '16 at 15:46
  • Further, delete cascade should not be used because having that error is a good thing, it tells you there are child records which for many types of tables might mean that you do not want to do the delete. For instance, if you deleted a customer and you had customer orders, cascaded delete would delete those as well and that would mess up your financial records. Many dbas do not permit cascade delete on their databases. – HLGEM Jan 13 '16 at 15:46
  • It is not a tricky question at all. If you understand what an FK relationship is, then it is a very straightforward question. I certainly would not hire anyone to do data work who could not answer this immediately and without having to think. This is database 101 stuff. – HLGEM Jan 13 '16 at 15:47
  • 1
    @HLGEM Hope you dont think so bad of me for this. I know you have to delete FK first. But the question start with `When we delete a primary key which...` to me that will cause an error. Or maybe is my english isnt so good as I tought. Anyway I wouldnt respond it without ask for more information first ;). – Juan Carlos Oropeza Jan 13 '16 at 15:52
  • @HLGEM I agree with your reasoning, yet I'm not with you when you say "I certanly would not hire anyone to do data work who could not answer this immediately". If the answer you expect is "the database should not have delete constraints" I would feel like the guy is trying to dodge the bullet rather than give an answer. On the other side, I would appreciate someone asking for more details before answering. – Cynical Jan 13 '16 at 18:31
  • No I meant someone who didn't understand FKs well enough to know the child records had to be deleted first whether they are deleted manually or through a cascade delete constraint.. – HLGEM Jan 13 '16 at 19:36
1

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186