cascading rules

Question

Post reply

cascading rules

Brian Smith-382016

Grasshopper

Points: 16
More actions
December 2, 2006 at 7:40 am

#173626

I am confused about cascading rules.
I have two tables:
client
ClientIdUniqueIdentifer
ShipToAddress UniqueIdentifer(fk)
BillToAddress UniqueIdentifer(fk)
Address
AddressIdUniqueIdentifer
In Sql Server Express, when I try to set up ShipToAddress
and BillToAddress both having cascading rules for update
and delete I get an error stating that:
'Address' table saved successfully 'Client' table
- Unable to create relationship 'FK_Client_BillToAddress'.
Introducing FOREIGN KEY constraint 'FK_Client_BillToAddress'
on table 'Client' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION,
or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
So my questions. I understand what the error is say, but I want
both billtoAddress and ShiptoAddress to have cascading rules and
it seems like I should be able to do this somehow. Could some one
please explain this to me. Thanks.
Second question.
Which are better to use in Sql Server as keys
UniqueIndentifer or Int?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Brian Smith-382016 Grasshopper Points: 16 More actions · Answer 1

OK I have tried more things to try to get this to work.

I tried the following:

client

ClientId UniqueIdentifer

ShipToAddress UniqueIdentifer null(fk)

BillToAddress UniqueIdentifer null(fk)

Then I tried to set both the rules

to delete rule set null

update rule no action

but this still gave me the same error as the first message.

This does not make sense to me. It seems that there should

be no conflict to update both billToAddress and ShipToAddress

to Nulls if the underlaying address for them is deleted and I

can't figure out why this is a conflict. Help please.

Also for my parent child table I am using cascade, when should I use cascade? As it is now, it seems that changes will cause a cascade up through all of the children to to the top which I am not sure I always want? How do I figure out when to use a cascade and when not to?, when to use a null and when not to????? Thanks for any help

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

You are confused by Microsoft documentation making simple subject complicated, what you are looking for is called DRI(declarative referential integrity) meaning if a references b then b must exist. To do Cascade on delete you choose Cascade and not No Action because No Action means the delete will not Cascade. The same is true with Update. It is very complex math but the implementation is very simple since version 2000. The two links below will help you in enabling it. Post again if you still have question. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177288.aspx

http://msdn2.microsoft.com/en-us/library/ms186973.aspx

Kind regards,
Gift Peddie

Brian Smith-382016 Grasshopper Points: 16 More actions · Answer 3

thank you for your post. I was not totally lost. I had tried cascade on delete first, but this gave me conflict. If you look at the original post you will see that I have two keys

in client that both need to be updated when address changes. This is crux of the problem. I am getting an error message saying that I can't have both ShipToAddr and BillToAddr (both in Client update when address updates this causes referential

integrity problems....circular path problems. But I don't know

how to fix this....No update is NOT the solution. How can I get two different keys in client to be able to update based on changes in address with a circular path problem?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

(This is crux of the problem. I am getting an error message saying that I can't have both ShipToAddr and BillToAddr (both in Client update when address updates this causes referential integrity problems....circular path problems.)

The above violate the core DRI requirement if a references b then b must exist, you are using two to reference one column.

That is not DRI, as I said in my original post it is a clean a to b mapping and not ab to b mapping as you are explaining to me, for that you need a trigger. To Cascade it must be one to one mapping. So you need a trigger and not a Cascade action as your post started. Hope this helps.

Kind regards,
Gift Peddie

Brian Smith-382016 Grasshopper Points: 16 More actions · Answer 5

thanks that does help....I will read up on triggers and figure out what I need to do.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

Gift Peddie

SSC Guru

Points: 73570

December 10, 2006 at 4:01 pm

#677140

I am glad I could help.

Kind regards,
Gift Peddie