January 29, 2011 at 2:48 am
Hi all,
I was making some modifications to my ERD and I need to make use of Cascade Delete/Update for a part of my structure. I know it can be dangerous but want to ask a question before implementation.
Pretty much, I have three tables with a FK relationship. The first table holds the client name (ClientInformation), the second tables holds the phone numbers -- they might have multiple (ClientInformation_PhoneNumbers) and the third table (ClientInformation_PhoneNumberType) holds what kind of phone number it is....
This is the scenario:
When a client is deleted from the ClientInformation table, I want the phone numbers deleted from the ClientInformation_PhoneNumbers table but nothing deleted from the ClientInformation_PhoneNumberType table.
If the phone numbers are deleted but not the client name, it wont matter because of how I setup the table.
Can I add CASCADE DELETE and CASCADE UPDATE on that relationship between ClientInformation and ClientInformation_PhoneNumbers and it wont go over to the other table -- ClientInformation_PhoneNumberType? Or do you think I should just do an SQL statement to delete all phone numbers where the PK = FK?
A screen shot of part of my ERD is attached.
Unless otherwise stated, I use Microsoft Visual Studio 2005 Professional and Microsoft SQL Server 2005.
January 30, 2011 at 1:08 am
Yes, because you have one table with two foreign keys.
Now, be careful here. If you cascade delete/update both those keys, any change in either table will affect the center one. So, if you delete a phone number type, you'll also delete every phone number of that type. Be aware of that consequence.
But to help your thinking, think of defining a foreign key as defining a subtable. Only changes higher in the hierarchy will affect lower ones. You are making your clientinformation_PhoneNumber table a subtable of the two higher ones, but neither directly affect each other between type and primary client data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 30, 2011 at 1:46 am
IMO your ClientInformation_PhoneNumberType table is a lookup table (aka parameter table) where you record a number of characteristics for a certain kind of parameter value.
Typically FK relationships should only be declared for real parent-child relationships.
That would mean no declared relationship for parameter tables.
Typically one would enforce correctness using a trigger and a delete process for the parameter table would be a process that checks all objects that have columns containing that parameter tables values.
If you declare the FK, be sure to _not_ declare it with cascading update nor delete for relationships with this parameter table.
Meaning, if you delete a parameter row, it should be prevented if the parameter value is still in use by any other object, because you probably overlooked something when deciding to delete that parameter row.
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply