Understanding Update and Delete Rules for Relation

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

Answer 2

A foreign key field can only store null or a value defined by the primary key field.

If you try to change the foreign key value to something not defined by a primary key you will get an error. Likewise if you try to change a primary key which has foreign key dependencies you will get an error... as an example

Models table
modelID (primary key)   model
1                       Jeep   
2                       Ford

Customer table
id    customer   modelID (foreign key of Models.modelID)
1     1234       1
2     2345       2

If you try to delete the Jeep record from Models you will get an error because customer 1234 has a modelID set to 1, and this if the foreign key. Likewise if I try to update customer 1234 to have a modelID of 3 it will throw an error because there is no primary key in the Models table having a value of 3

Check this out

Answer 3

The MSDN page looks like a good start.

Answer 4

It looks like the documentation is at Foreign Key Relationships Dialog Box.

BTW, F1 help worked fine for me in SSMS 2008. It took me right to the above page (after I searched for 1/2 hour online, of course).