OK, so for interactive activities, where a user is directly maintaining
a database, I agree that an administrator that knows what s/he is doing
would be the most likely person performing these types of activities.
And hopefully they would be aware of the possibility of orphaned
records and remember to execute deletes in pairs.

What I was thinking about was an application that downloads data on a
regular basis and keeps a log of all the records downloaded, but needs
to purge them to only keep the last 30 days worth or so of records.
Sure, putting the deletion of the master and detail records in a
transaction is the obvious and trivial way to do it.  Of course, the
same thing can be done through triggers!

I would say that from a purely database design strategy, I would want
enforced referential integrity in the way of cascading deletes if for
no other reason than for self-documentation, to remind oneself that the
records go together and you can not delete one without the other.  But
of course, there are many other ways to document database schemas.

I suppose that with the release of Elevate DB and the new level of
database features and SQL standards, everyone now suddenly has their
level of expectations rise.  It might feel at times like a no-win
situation for you: the moment you raise the bar, people suddenly wish
it was even higher!

--
David Cornelius
CorneliusConcepts.com
custom designed software


Tim Young [Elevate Software] wrote:

> David,
>
> << Are you saying specifically that cascading deletes (and other
> referential activities) should not be supported by applications, but
> only done manually by administrators? >>
>
> No, I'm saying that these types of activities are normally
> security-constrained anyways and only available to higher-end users
> like administrators, etc., if at all.  The fact that your application
> executes such functionality internally really doesn't apply, since it
> isn't being executed by an interactive user.
>
> << I can think of a situation where cascading deletes in an
> application would be very nice and used quite frequently, but maybe
> I'm not understanding what you're referring to... >>
>
> Are you talking about cascading deletes initiated by a user, or just
> cascading deletes in application code ?
>
> At any rate, cascading updates and deletes can be done via a
> transaction manually, and that's exactly what EDB would do itself,
> except that it would have to use even more restrictive locking to do
> so.  This was the main reason why it wasn't implemented.

Steve,

<< Just for completeness, hate for you to take it out <BG> >>

Nah, that won't be happening.

--
Tim Young
Elevate Software
www.elevatesoft.com

David,

<< OK, so for interactive activities, where a user is directly maintaining a
database, I agree that an administrator that knows what s/he is doing would
be the most likely person performing these types of activities. And
hopefully they would be aware of the possibility of orphaned records and
remember to execute deletes in pairs. >>

If you have the RI set up properly, it will be impossible for even the
administrator to not delete rows in the proper sequence without getting a
foreign key constraint error.  IOW, orphaned rows would be impossible in
such a scenario.

<< What I was thinking about was an application that downloads data on a
regular basis and keeps a log of all the records downloaded, but needs to
purge them to only keep the last 30 days worth or so of records. Sure,
putting the deletion of the master and detail records in a transaction is
the obvious and trivial way to do it.  Of course, the same thing can be done
through triggers! >>

Sure, you could use a trigger also along with the RI/Restrict functionality
to ensure that no mistakes are made.

<< I would say that from a purely database design strategy, I would want
enforced referential integrity in the way of cascading deletes if for no
other reason than for self-documentation, to remind oneself that the records
go together and you can not delete one without the other.  But of course,
there are many other ways to document database schemas. >>

Well, you already have the RESTRICT functionality, so any foreign key
constraint definitions will document the relationships when you
reverse-engineer the database in the EDB Manager.

<< I suppose that with the release of Elevate DB and the new level of
database features and SQL standards, everyone now suddenly has their level
of expectations rise.  It might feel at times like a no-win situation for
you: the moment you raise the bar, people suddenly wish it was even higher!
>>

Nah, it's no problem.  I'm just trying to let everyone know that it isn't
such a great loss for certain features to be missing.  The most important
part of RI is to *prevent* improper data from getting into the database and
to preserve the integrity of the database as a whole.  The fact that EDB
doesn't do cascade operations at this point is secondary in terms of
importance when you look at a) their frequency, b) how such operations are
usually performed, and c) who usually performs such operations.

--
Tim Young
Elevate Software
www.elevatesoft.com

Good discussion--thanks so much, Tim, for the time you take to answer
all these messages!

--
David Cornelius
CorneliusConcepts.com
custom designed software