[ACCEPTED]-Understanding Update and Delete Rules for Relationships in SSMS 2008-foreign-keys
The foreign key defines a parent - child 24 relationship between two tables. The primary 23 key in the parent table is the foreign key 22 in the up to n child table rows.
Now if that 21 primary key in the parent table gets UPDATE, the 20 UPDATE RULE kicks in. Either all the child 19 rows are also updated, set to NULL or whatever. Best 18 practice however is to have a primary key 17 that NEVER changes (a fixed ID or something), so 16 that's the less important rule.
The more 15 important one is the DELETE rule - what 14 if the parent row is deleted (e.g. the Order 13 is deleted)? You can either also delete 12 all child rows (all the Order line items) with 11 CASCADE DELETE, or you can set their foreign 10 key to NULL (they don't have a parent anymore) - that's 9 totally up to your concrete scenario.
In 8 the Order/order lines scenario, it might 7 be totally useful to delete the order lines 6 when the complete order gets deleted, but 5 you probably don't want to delete a product, just 4 because an order that references it has 3 been deleted - there's no one single CORRECT 2 answer - it depends on your scenario and 1 your app.
Marc
It looks like the documentation is at Foreign Key Relationships Dialog Box.
BTW, F1 3 help worked fine for me in SSMS 2008. It 2 took me right to the above page (after I 1 searched for 1/2 hour online, of course).
A foreign key field can only store null 14 or a value defined by the primary key field.
If 13 you try to change the foreign key value 12 to something not defined by a primary key 11 you will get an error. Likewise if you try 10 to change a primary key which has foreign 9 key dependencies you will get an error... as 8 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 7 record from Models you will get an error 6 because customer 1234 has a modelID set 5 to 1, and this if the foreign key. Likewise 4 if I try to update customer 1234 to have 3 a modelID of 3 it will throw an error because 2 there is no primary key in the Models table 1 having a value of 3
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.