SQL Insert and Update specifications

Consider this scenario:

You have two tables, „Customer“ and „CustomerGroup“. Our customer table contains a foreign key „customergroup_FK“ to associate groups to our customers. Now we want to enable the user to delete some of our customer groups.

One time-consuming solution would be:

1. Delete the customer group,

2.select all customer where customergroup_FK fits our deletedGroup_PK

3.Update them for example to the NULL value.

Instead of this approach we can delegate this work directly to SQL Server:

Open your database diagram select „Relations“ and go to your „Customer_CustomerGroup“ relation. Now  on „UPDATE – and INSERT specifications“ select Delete Rule => set NULL

Now, since SQL Server do the update stuff for us, we only have to delete the customer group and maybe refresh the data source. That’s all. Very nice, isn’t it 🙂