Foreign key constraint - how to delete referenced record?

Question

I stumbled across a problem, which is very basic, thus didn't really think it through.

I have two tables, let's say:

Table1: col1 PK,
Table2: col1 FK references Table1(col1)

When I want to delete record from Table1, which is referenced by record in Table2, SQL would throw error, that DELETE statement coflicted with a constraint. Which is understandable and desired behaviour.

What if I really want to delete the row and set reference in Table2 to NULL in all rows referencing key being deleted?

I know that I can do it manually - set references in Table2 to NULL and then delete. But is there more concise way?

Answer 1

You could configure Insert and Update Specification rule by setting Set Null option on FK relationship. I have attached an image from Sql Server Management Studio. Also, there is a useful article here.

Answer 2

You can alter your database structure by adding a delete rule, set to null OR onDeleteCascade

Answer 3

I am posting this answer as an alternative which actually gets around the need to even null out the foreign key references in Table2. You may consider performing soft deletion on the records in Table1. What this means is that you would maintain a new column deleted which would be set to true if the record is logically deleted.

The advantage of this method is that you may leave all the child records in Table2 completely intact, including their references. But logically speaking, the record in Table1 could be treated as deleted. This is not an option if space be a concern, e.g. if you really need to remove records from your tables.