Re: multiple cascade paths

On Mon, 11 May 2009 08:29:44 -0700 (PDT), shripaldalal@xxxxxxxxx wrote:

Hello Hugo,

Thanks for the reply. Just a few more things:

Okay. I'll address some and snip the rest of the post.

Also, your model would fail with a primary key violation if goods ever
has to be moved between the same two departments again (eg. another move
from collection to logistics). And what's worse, you can't reconstruct
the path from the data - the exact same set of rows would also be used
to represent logistics --> collection --> logisctics --> testing -->
logisctics. In the real world, you'd need to add some property to
represent the sequence of transfers. (Or do I misunderstand the purpose
of this table?)

You misunderstood the purpose of this table, my friend! ;-)

So I obviously did.

Yes, with this table being a list of allowed movements, the above
problems do not apply. Sorry for assuming too much. (And there I go
berating Joe Celko over assuming too much - worst possible timing to
make the exact same mistake myself... :D)

(snip)
I have made the entire application on MS Access and it's working
wonderfully. The only reason to go to SQL Server now is because right
now 20 users are using it soon 300 will. Secondly Access has the 2GB
data limit. I have absolutely no problem with it!
(snip)
Stored procedures and triggers! Development time increases and so does
schema documentation! :-)

Well, that is indeed a nasty problem. You can stay on Access with the
risk of problems as it starts to outgrow its limits. You can port to SQL
Server but then you'll have to do more work to implement some of your
cascading deletes in some other way. Or you can port to a different DBMS
that does not have this limitation, but than the porting itself will
probably be more work.

I'm sorry, but I can't make this any easier for you than it is...

Except by saying once more that, in my experience,
a) most foreign key constraints don't form cycles or multiple cascade
paths; and
b) most foreign key constraints do not need cascading; and therfor
c) most applications that run into this problem only have to replace a
very small amount of cascading constraints with some other solution.

The number you meniton (20-30) is, in my experience, extremely high. The
need for multiple cascade paths is very clear in the example you gave
here, but are you really sure that the need is just is correct in all
the other cases?

If it is, then you do have indeed a tough choice to make. But if it
isn't, then the option to move to SQL Server should start sounding a
much more promising.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.

Relevant Pages

  • Re: multiple cascade paths
    ... I hate the multiple cascade paths error. ... SQL Server has no problem with multiple foreign key constraints between ... It still has no problem when one of them is cascading. ...
    (comp.databases.ms-sqlserver)
  • Re: Cascading Changes in SQL Server
    ... is this article saying that SQL server ... >> relationship had cascading deletes and updates. ... or modify other FOREIGN KEY constraints. ...
    (microsoft.public.sqlserver.server)
  • Re: Migrating away from MS-Access
    ... that, in fact, the amount of information being sent *is* smaller. ... convert the back end to SQL Server and fix the Access ... I do think that it is definitly the case the front end needs a 70-80% rewrite to work with sqlserver There must be reasons why people develop in VB.net. ...
    (comp.databases.ms-access)
  • Re: UPDATE Statements May be Replicated as DELETE/INSERT Pairs
    ... and I'm not updating the primary key, that is why I don't really understand ... you have cascading updates and deletes and you have not checked the ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL: If one item is false, I need a false returning
    ... have another field, RepairAuthType. ... Some well-chosen rows of sample data, ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.programming)