Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jammydodger1000
Starting Member
3 Posts |
 Posted - 2006-08-27 : 20:28:17
|
| Hello all,I am new to SQL and I was hoping someone could explain something to me about 'on update cascade'.I understand what 'on update cascade' does (i think) - it updates the child table when the parent table is updated.What i do not understand is that the 'on update cascade' works on the primary key of the parent table, but i was on the understanding that the primary key doesn't change often if at all so why would the 'on update cascade' be of use?Sorry for my ignorance and i realise i must be missing something simple but would be grateful for any help.Thanks |
|
|
Rishi Maini SQL2K5 Admin
Yak Posting Veteran
80 Posts |
Posted - 2006-08-28 : 03:02:57
|
| Hey Jammy,You exactly got the 'on update cascade' definition right. I agree that the value in the Primary Key Column doesn't change very often but if at all it changes I do not want to get the pain in changing the same value in all my child tables. I want SQL to handle this for me and this is exactly what the 'on update cascade' is doing for me. So it totally depends on your Business Logic , at your end you rarely change the Primary Key but may be some business logic in some other company requires frequent changes in the Primary Key like incrementing the Primary Key value by "1" etc.ThanksRishi Maini |
 |
|
|
jammydodger1000
Starting Member
3 Posts |
Posted - 2006-08-28 : 07:34:48
|
| Thanks for your reply Rishi,You have helped me, would it also be possible to give me an example of a table where the primary key might change to reinforce my understanding. If I can picture an example in my head its going to help me alot.Thanks |
|
|
|
Rishi Maini SQL2K5 Admin
Yak Posting Veteran
80 Posts |
Posted - 2006-08-28 : 08:18:32
|
| In my Previous company we used to have a Table that used to store the "ITEM" information for all the Raw Materials used in the Company.create table Item_info (item_code char(6) primary key, item_name varchar(100))Select * from Item_infoitem_code item_name --------- --------------000001 Acetic Acid000002 Brufen000003 Ethyl AcetateSuddenly My Manager came to me and said that Due to some Business Needs and Company Norms we have Decided to start the Item_Code with the First Alphabet of the Item Name.Which Means:- item_code item_name New Item Code --------- --------------000001 Acetic Acid A00001 000002 Brufen B00002 000003 Ethyl Acetate E00003So I ran a statment as :- "update item_info set Item_code= substring(item_name,1,1) + substring(item_code,2,5)"Now the new Item_Code got Modified as Desireditem_code item_name --------- ----------------A00001 Acetic AcidB00002 BrufenE00003 Ethyl AcetateNow being the "on update cascade" in place the Item Code was modified in all the dependent tables like Invoice Details etc....just Imagine the pain that I need to go if this option was not available. In that case first I need to identify each and every table in my Database where this Item_Code is being used and then go manually and update the Item Code there also.I am not sure if this is the best example or not...I think you were looking more into a scenario where the Primary Key Field is changed on Regular Basis.....If I can think of one I will certainly include it in the post.ThanksRishi Maini |
|
|
|
jammydodger1000
Starting Member
3 Posts |
Posted - 2006-08-28 : 08:48:56
|
| Thanks again Rishi for your time in writing that, it all helps. Any more examples would be appreciated if you can come up with any. |
|
|
|
PsyckBoy
Starting Member
1 Post |
Posted - 2006-09-01 : 18:09:03
|
| You should also be aware that you can create foreign keys that point to unique indexes in other tables, not just to primary keys. These indexes probably contain multiple columns, and if you update one of these columns in the parent table, it's nice to see the change propagate to all child tables. |
|
|
|
|
|
|
|
|
on update cascade