When do we use on delete set null and on delete cascade?

Data Science, AI and ML
,
#1

Sql, Set

#2
  • ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted.
  • ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table.
#3

Maybe an example is better.

Let’s say you have an invoice which consists of client, data, invoice no. etc in a table.

Let’s consider that the lines of the invoice are in another table.

Let’s say that for some reason you delete one record from invoices table.

Do we still need the lines ? Who do they belong to ? When were they made ?

So if you erase the header you should erase the lines also.

To do that automatically you say “ on delete cascade” when you define the foreign key.