Hello,
I wonder why advanced and powerful database like elevatedb did not has a
cascade update/delete for foreign keys !!!

Isaac,

<< I wonder why advanced and powerful database like elevatedb did not has a
cascade update/delete for foreign keys !!! >>

How often do you change the primary keys in your databases ?  If you do it a
lot, then you probably need to look at changing your design a bit.
Cascading such updates is extremely expensive in terms of concurrency,
especially with a lot of rows, which is why EDB does not allow it at this
time.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

The cascade on delete in foreign keys is IMO an important thing, it's
very useful. I have not seen yet an use for cascade update, so I agree
about that it's a design issue, but I would not exclude it.
Firebird/IB support On Delete and On Update this options: CASCADE,
UPDATE, SET NULL, RESTRICT.

Julio S.


> Isaac,
>
> << I wonder why advanced and powerful database like elevatedb did not has a
> cascade update/delete for foreign keys !!! >>
>
> How often do you change the primary keys in your databases ?  If you do it a
> lot, then you probably need to look at changing your design a bit.
> Cascading such updates is extremely expensive in terms of concurrency,
> especially with a lot of rows, which is why EDB does not allow it at this
> time.
>

Julio,

<< The cascade on delete in foreign keys is IMO an important thing, it's
very useful. I have not seen yet an use for cascade update, so I agree about
that it's a design issue, but I would not exclude it.Firebird/IB support On
Delete and On Update this options: CASCADE, UPDATE, SET NULL, RESTRICT. >>

I understand what the other database engines out there support, and I
understand the issues involved.  The point is that most of the time, such
changes are a one-off that should be executed by an administrator or someone
who knows what they're doing due to the amount of locks, etc. that need to
be held for such an operation to successfully complete.

--
Tim Young
Elevate Software
www.elevatesoft.com

Julio S. wrote:
> Tim,
>
> The cascade on delete in foreign keys is IMO an important thing, it's
> very useful. I have not seen yet an use for cascade update, so I agree
> about that it's a design issue, but I would not exclude it.
> Firebird/IB support On Delete and On Update this options: CASCADE,
> UPDATE, SET NULL, RESTRICT.
>
> Julio S.
>
>
>> Isaac,
>>
>> << I wonder why advanced and powerful database like elevatedb did not
>> has a cascade update/delete for foreign keys !!! >>
>>
>> How often do you change the primary keys in your databases ?  If you
>> do it a lot, then you probably need to look at changing your design a
>> bit. Cascading such updates is extremely expensive in terms of
>> concurrency, especially with a lot of rows, which is why EDB does not
>> allow it at this time.
>>
I second your opinion exactly - I hope future releases has this feature

Hi Tim,

My vote is strongly in favour of a partial implementation as detailed below.

I believe that support for CASCADE and RESTRICT on DELETE is needed as there
is a common requirement for these operations in many business schemas. I can
think of several situations where I would use both these ops in my current
applications if they were available.UPDATE does not apply to a DELETE, and
only someone with rocks in their head would delete a master record and leave
orphans, so SET NULL is definitely not needed IMO

I do not think you need to support UPDATE because I cannot think of any
valid reason to modify a table's primary key. I agree that this type of
operation should be done by a DBA, or someone with knowledge of the cause
and effect of such an operation.

--

Best Regards

Steve
[Team Elevate]

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:59E3CEC0-E772-46DA-944A-89AD919AF8B2@news.elevatesoft.com...
> Julio,
>
> << The cascade on delete in foreign keys is IMO an important thing, it's
> very useful. I have not seen yet an use for cascade update, so I agree
> about that it's a design issue, but I would not exclude it.Firebird/IB
> support On Delete and On Update this options: CASCADE, UPDATE, SET NULL,
> RESTRICT. >>
>
> I understand what the other database engines out there support, and I
> understand the issues involved.  The point is that most of the time, such
> changes are a one-off that should be executed by an administrator or
> someone who knows what they're doing due to the amount of locks, etc. that
> need to be held for such an operation to successfully complete.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Tim,

Are you saying specifically that cascading deletes (and other
referential activities) should not be supported by applications, but
only done manually by administrators?

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...

--
David Cornelius
CorneliusConcepts.com
custom designed software


Tim Young [Elevate Software] wrote:

> Julio,
>
> << The cascade on delete in foreign keys is IMO an important thing,
> it's very useful. I have not seen yet an use for cascade update, so I
> agree about that it's a design issue, but I would not exclude
> it.Firebird/IB support On Delete and On Update this options: CASCADE,
> UPDATE, SET NULL, RESTRICT. >>
>
> I understand what the other database engines out there support, and I
> understand the issues involved.  The point is that most of the time,
> such changes are a one-off that should be executed by an
> administrator or someone who knows what they're doing due to the
> amount of locks, etc. that need to be held for such an operation to
> successfully complete.

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.

--
Tim Young
Elevate Software
www.elevatesoft.com

Steve,

<< My vote is strongly in favour of a partial implementation as detailed
below. >>

EDB can't do it right now, so any further discussion is basically pointless.
The locking model in EDB, due to the multi-user file-sharing support, won't
allow it without extremely restrictive locking.  It's something that will
have to wait until the enterprise server is done.

The way to do it is to simply use a transaction.  EDB will still ensure that
the referential integrity is maintained, which is its main job.

<< I believe that support for CASCADE and RESTRICT on DELETE is needed as
there is a common requirement for these operations in many business schemas.
>>

ElevateDB already does the RESTRICT functionality.  Or were you just
throwing that in there to complete the minimum spec that you're proposing ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> EDB can't do it right now, so any further discussion is basically
> pointless.

NP, just thought I'd put my 2c worth in

> ElevateDB already does the RESTRICT functionality.  Or were you just
> throwing that in there to complete the minimum spec that you're proposing
> ?

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

--

Best Regards

Steve
[Team Elevate]

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:7B1B98D9-F0D3-4AED-9ADF-A01772C96179@news.elevatesoft.com...
> Steve,
>
> << My vote is strongly in favour of a partial implementation as detailed
> below. >>
>
> EDB can't do it right now, so any further discussion is basically
> pointless. The locking model in EDB, due to the multi-user file-sharing
> support, won't allow it without extremely restrictive locking.  It's
> something that will have to wait until the enterprise server is done.
>
> The way to do it is to simply use a transaction.  EDB will still ensure
> that the referential integrity is maintained, which is its main job.
>
> << I believe that support for CASCADE and RESTRICT on DELETE is needed as
> there is a common requirement for these operations in many business
> schemas.
> >>
>
> ElevateDB already does the RESTRICT functionality.  Or were you just
> throwing that in there to complete the minimum spec that you're proposing
> ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>