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


Sql, Set

  • 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.

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.