Understanding Update and Delete Rules for Relationships in SSMS 2008

Question

I am confused about what means the update and delete rule in SQL Server 2008 Management Studio when we define foreign key constraints. I also did not find related help documents (e.g. F1 help).

Here is the screen snapshot. Appreciate if anyone could describe what do they mean and recommend some related documents to read. :-)

Answer 1

The foreign key defines a parent - child relationship between two tables. The primary key in the parent table is the foreign key in the up to n child table rows.

Now if that primary key in the parent table gets UPDATE, the UPDATE RULE kicks in. Either all the child rows are also updated, set to NULL or whatever. Best practice however is to have a primary key that NEVER changes (a fixed ID or something), so that's the less important rule.

The more important one is the DELETE rule - what if the parent row is deleted (e.g. the Order is deleted)? You can either also delete all child rows (all the Order line items) with CASCADE DELETE, or you can set their foreign key to NULL (they don't have a parent anymore) - that's totally up to your concrete scenario.

In the Order/order lines scenario, it might be totally useful to delete the order lines when the complete order gets deleted, but you probably don't want to delete a product, just because an order that references it has been deleted - there's no one single CORRECT answer - it depends on your scenario and your app.

Marc