| Subject | Re: [ib-support] cascading delete |
|---|---|
| Author | Helen Borrie |
| Post date | 2003-03-09T13:55:59Z |
At 03:17 PM 9/03/2003 +0200, you wrote:
Actually, it doesn't have anything to do with it. Foschi's example at
least had integrity...The problems you encountered have to do with the
(inadvisable) use of meaningful data as foreign keys and the consequent
problems stemming therefrom. Your setup will inevitably break
relationships that really shouldn't be broken, by cascading whenever a user
fixes a typo or even accidentally touches the spacebar.
It's reasonable to put a unique constraint on the PM's name, to avoid the
situation where both Jane and John decide they want to be J. Doe. But you
should protect the integrity of the relationships here by linking foreign
keys to the primary keys, not to the unique constraints. That way, the
relationships hold, no matter how Jane wants to spell her name, or if
someone else takes over one of Jane's groups, or if Jane gets married.
It's a question of designing your keys so that (a) they will cascade in the
right sequence and (b) they won't cascade when they are not intended to. A
lot of the time, you can do it with the auto RI triggers, with some careful
attention to the downstream relationships. If you want a special cascade
order in the Worlds/Planets/Stars case, write your own triggers, or create
some intersecting structure that takes care of the relationship between the
planets and the stars. The DB engine doesn't have a crystal ball...
I'm not reposting all of your DDL. This kind of question isn't one I would
consider worth debating...
heLen
>Helen Borrie wrote:Lauri,
> >
> > On what basis do you think FB could "smart-guess" or determine an optimal
> > sequence for two constraints that are unrelated to each other?
>
>If that can not be done, then a mechanism for
>controlling (and checking) foreign key check-and-cascade orders would be
>nice.
>
> > At best, it might perform the cascade on the basis of the creation
> order of
> > the foreign keys, or some other arbitrary metadata thing -- alphabetical
> > order? Have you considered running your own predictability tests?
>
>I have attached the two scripts that i was talking about previously.
>
>(I'm sorry for cutting in to this thread, but it has everything to do
>with my last post (s: fk problem on 07.03.03))
Actually, it doesn't have anything to do with it. Foschi's example at
least had integrity...The problems you encountered have to do with the
(inadvisable) use of meaningful data as foreign keys and the consequent
problems stemming therefrom. Your setup will inevitably break
relationships that really shouldn't be broken, by cascading whenever a user
fixes a typo or even accidentally touches the spacebar.
It's reasonable to put a unique constraint on the PM's name, to avoid the
situation where both Jane and John decide they want to be J. Doe. But you
should protect the integrity of the relationships here by linking foreign
keys to the primary keys, not to the unique constraints. That way, the
relationships hold, no matter how Jane wants to spell her name, or if
someone else takes over one of Jane's groups, or if Jane gets married.
It's a question of designing your keys so that (a) they will cascade in the
right sequence and (b) they won't cascade when they are not intended to. A
lot of the time, you can do it with the auto RI triggers, with some careful
attention to the downstream relationships. If you want a special cascade
order in the Worlds/Planets/Stars case, write your own triggers, or create
some intersecting structure that takes care of the relationship between the
planets and the stars. The DB engine doesn't have a crystal ball...
I'm not reposting all of your DDL. This kind of question isn't one I would
consider worth debating...
heLen