September 12, 2008 at 12:15 am
In SQL2005,I have to update the primary key of a table which is referenced by many child tables.The on update cascade is not a preferred option.Is there any other option to proceed with it other than on update cascade?
September 12, 2008 at 12:47 am
Gayathri.Varadarajan (9/12/2008)
...The on update cascade is not a preferred option.....
Then don't update PKs !
If you have FKs pointing to that parent table (I sure hop so), and you realy need to update the PK, I wouldn't want another method than cascading update !
Be sure to have all your FKs supported by a correct index.
(one that totally matches your PK ![nothing else] ).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2008 at 2:13 am
another work around is..
drop the foreign key relation ship update values in both the tables and again create the foreign key relationship between tables.
🙂
September 12, 2008 at 2:46 am
That is indeed a valid workaround if this is a singleshot operation (upgrade, bugfix,...)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2008 at 2:53 am
That work around of dropping constraints wont work as this updation of PK process may be triggered from the application at any point of time and it may happen even daily.Also the business functionality is so that the PK has to be updated.So there should be some other way in which the problem has to be tackled.In ORACLE SET Deferred Constraints option which is not available in SQL 2005.
September 12, 2008 at 3:09 am
Many times, this kind of issue points to the upmost advantage of using surrogate keys (which don't have a symantic payload at all, but their strict value).
Change of the parent table unique constraint key info content (i.e. your current meaningfull key info) does not have to be cascaded to all depentant objects.
Impact:
- all your join operations must use the surrogate keys for join predicate
- If you need parent table information (i.e. your current meaningfull key info), you'll need an extra join.
If this is a developing project, maybe it's time to implement surrogate keys.
Dr. Codds rules are still applicable.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2008 at 3:20 am
This is valid if the project is a development project.But the feature of updating PK alone is a new one on the existing product.So the surrogate key will also not help.
In fact the PK which i have referred to in the forum is itself the surrogate key and all the child relationships are based on this key.The actual PK is an identity column.I dint want to confuse stating it as a surrogate at the beginning itself.Wanted to keep the problem simple 🙂
September 12, 2008 at 4:02 am
Why don't you wanna use update cascade?
September 12, 2008 at 4:04 am
just a small help for you...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
September 12, 2008 at 4:06 am
just a small help for you...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
😛 is ": and P"
September 12, 2008 at 4:43 am
The table contains millions of records and there are more than 30 child tables which goes to 7-8 levels.The on update cascade will be too costly and also risky to be executed.
September 12, 2008 at 5:20 am
Gayathri.Varadarajan (9/12/2008)
T...In fact the PK which i have referred to in the forum is itself the surrogate key and all the child relationships are based on this key.The actual PK is an identity column.I dint want to confuse stating it as a surrogate at the beginning itself.Wanted to keep the problem simple 🙂
:blink::blink::blink::blink:
- How come a surrogate key changes value ????
- IMO this can only exist if you copy the original parent row, in a non-nomalized (Codd) environment, and then, you're not updating the PK, you're re-attaching the child rows.
Although I have my doubts with such operations.
- If you actually perform an update of the PK-column, don't you run into issues with the identity property ?
- If you actually perform an update of the PK-column, I think the cascading update is your best option. Leave FK in place !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 12, 2008 at 5:54 am
Gayathri.Varadarajan (9/12/2008)
...Also the business functionality is so that the PK has to be updated...
At this point, you're totally violating the idea of a primary key. You may have a unique constraint on the column or columns that you're referring to as the PK, but if you're constantly updating these values, they can't be the PK. You should either reevaluate to see other columns constitute a better natural key or you may need to implement an artificial key in this circumstance. You absolutely should not be updating the keys in this manner.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 12, 2008 at 8:10 am
I have to agree with Grant, the design is flawed and you should be using a surrogate key if what you are currently using for a primary key field is a value that needs to be editable. Cascading changes like this really violate the rules of normalization.
As far as a solution, you need to assign all of your child records to a foster parent. Create a new record in the primary key table, assign all child records to the new record and delete the original - or if you prefer, modify the original, re-assign all of the children, and delete the foster record (not that there is much benefit in that).
This is one of the areas that I have seen a lot of Oracle development use the deferred constraints and (in my opinion) it is really a case of using a pretty cool feature of Oracle to supplement a poor database design.
September 12, 2008 at 8:17 am
Another option, not necessarily a good one, is to add a version column to your PK and then only do inserts. You can then do one of two things. Insert all the child data each time the parent is "updated" or, drop the FK constraints and use triggers to enforce the constraints between parent & child.
I'd go with redesigning the key structure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply