Thread: One more time on ONE-TO-MANY
CC me, I'm digesting this list.
From:
http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3
A quote:
' In addition *the database designer chooses* an action for delete:
* It's /only possible/ to delete a row in the one-table when there a
no more related many-rows.
* When deleting a row the RDBMS
<http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3#rdbms>
/automatically/ deletes the related data in the many table. This
is called a /cascaded delete/.
* When deleting the last 'many' the RDBMS /automatically/ deletes
the related 'one' row.'
I'm pretty sure that Postgres does not support the last one
automatically. I shall have to do that one by either a chron script or a
post trigger.
Does anyone have experience with a database that will do the last one,
and what database would that be?
It sounds like you are referring to a RI (Referential Integrity) constraint and if so one of the options when the constraint is defined is CASCADE DELETE, i.e., delete the children rows then delete the parent row and this is available in Postgres.
Is this what you were asking or did I mis-interpret your query?
Duane
-----Original Message-----
From: Dennis Gearon [mailto:gearond@fireserve.net]
Sent: Wednesday, May 26, 2004 3:25 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] One more time on ONE-TO-MANY
CC me, I'm digesting this list.
From:
http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3
A quote:
' In addition *the database designer chooses* an action for delete:
* It's /only possible/ to delete a row in the one-table when there a
no more related many-rows.
* When deleting a row the RDBMS
<http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3#rdbms>
/automatically/ deletes the related data in the many table. This
is called a /cascaded delete/.
* When deleting the last 'many' the RDBMS /automatically/ deletes
the related 'one' row.'
I'm pretty sure that Postgres does not support the last one
automatically. I shall have to do that one by either a chron script or a
post trigger.
Does anyone have experience with a database that will do the last one,
and what database would that be?
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Yes, and No. I am talking about that capability, which is the first two in the list, pluse another, the last one in the list. That is when an attempt to delete the LAST CHILD takes place, the logic is that there is no need for the parent, or that there must be a child for every parent. So, either the action is to say NO, error out, or the parent is deleted along with the child. Currently, all Postgres supports natively is what should happen if the PARENT is deleted. Supposedly, some RDBMs handle the options of when the LAST child is deleted, natively, by declaration in the constraint. Duane Lee - EGOVX wrote: > It sounds like you are referring to a RI (Referential Integrity) > constraint and if so one of the options when the constraint is defined > is CASCADE DELETE, i.e., delete the children rows then delete the > parent row and this is available in Postgres. > > Is this what you were asking or did I mis-interpret your query? > > Duane > > -----Original Message----- > From: Dennis Gearon [mailto:gearond@fireserve.net] > Sent: Wednesday, May 26, 2004 3:25 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] One more time on ONE-TO-MANY > > > CC me, I'm digesting this list. > > From: > http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3 > A quote: > ' In addition *the database designer chooses* an action for delete: > > * It's /only possible/ to delete a row in the one-table when there a > no more related many-rows. > * When deleting a row the RDBMS > <http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3#rdbms> > /automatically/ deletes the related data in the many table. This > is called a /cascaded delete/. > * When deleting the last 'many' the RDBMS /automatically/ deletes > the related 'one' row.' > > I'm pretty sure that Postgres does not support the last one > automatically. I shall have to do that one by either a chron script or a > post trigger. > > Does anyone have experience with a database that will do the last one, > and what database would that be? > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Ok, I think I understand now. You are wondering if the capability exists that whenever all the children of a parent are deleted, not via cascade delete, can the parent also be deleted. I know this capability does not exist in DB2 and I'm pretty sure it doesn't in Postgres. Personally, that is not a "feature" I would choose, especially as any kind of default.
Duane
-----Original Message-----
From: Dennis Gearon [mailto:gearond@fireserve.net]
Sent: Wednesday, May 26, 2004 5:25 PM
To: Duane Lee - EGOVX
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] One more time on ONE-TO-MANY
Yes, and No. I am talking about that capability, which is the first two
in the list, pluse another, the last one in the list.
That is when an attempt to delete the LAST CHILD takes place, the logic
is that there is no need for the parent, or that there must be a child
for every parent.
So, either the action is to say NO, error out, or the parent is deleted
along with the child.
Currently, all Postgres supports natively is what should happen if the
PARENT is deleted.
Supposedly, some RDBMs handle the options of when the LAST child is
deleted, natively, by declaration in the constraint.
Duane Lee - EGOVX wrote:
> It sounds like you are referring to a RI (Referential Integrity)
> constraint and if so one of the options when the constraint is defined
> is CASCADE DELETE, i.e., delete the children rows then delete the
> parent row and this is available in Postgres.
>
> Is this what you were asking or did I mis-interpret your query?
>
> Duane
>
> -----Original Message-----
> From: Dennis Gearon [mailto:gearond@fireserve.net]
> Sent: Wednesday, May 26, 2004 3:25 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] One more time on ONE-TO-MANY
>
>
> CC me, I'm digesting this list.
>
> From:
> http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3
> A quote:
> ' In addition *the database designer chooses* an action for delete:
>
> * It's /only possible/ to delete a row in the one-table when there a
> no more related many-rows.
> * When deleting a row the RDBMS
> <http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3#rdbms>
> /automatically/ deletes the related data in the many table. This
> is called a /cascaded delete/.
> * When deleting the last 'many' the RDBMS /automatically/ deletes
> the related 'one' row.'
>
> I'm pretty sure that Postgres does not support the last one
> automatically. I shall have to do that one by either a chron script or a
> post trigger.
>
> Does anyone have experience with a database that will do the last one,
> and what database would that be?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
I agree with your statement. I believe that the SQL standard does not support the 3rd option by design. I could be wrong, but everything I've seen only discusses the first 2 options. If a RDBMS does that automatically, think about this possible consequence. Lets say that you have a database that tracks customers and their purchases. Lets say that an operator has the ability to delete purchase records (children (FK) to the customer (PK)). Lets say that a purchase gets entered by mistake while the operator is on the phone with the customer, and he/she needs to delete the mistake. As she deletes the mistake, the customer record is wiped out along with it. Then she has to re-create the customer record as the customer gets impatient because this information has already been given. I would much rather have to manually remove a childless parent record than have the system do it for me. Duane Lee - EGOVX wrote: >Ok, I think I understand now. You are wondering if the capability exists >that whenever all the children of a parent are deleted, not via cascade >delete, can the parent also be deleted. I know this capability does not >exist in DB2 and I'm pretty sure it doesn't in Postgres. Personally, that >is not a "feature" I would choose, especially as any kind of default. > >Duane > >-----Original Message----- >From: Dennis Gearon [mailto:gearond@fireserve.net] >Sent: Wednesday, May 26, 2004 5:25 PM >To: Duane Lee - EGOVX >Cc: pgsql-general@postgresql.org >Subject: Re: [GENERAL] One more time on ONE-TO-MANY > > >Yes, and No. I am talking about that capability, which is the first two >in the list, pluse another, the last one in the list. > >That is when an attempt to delete the LAST CHILD takes place, the logic >is that there is no need for the parent, or that there must be a child >for every parent. >So, either the action is to say NO, error out, or the parent is deleted >along with the child. > >Currently, all Postgres supports natively is what should happen if the >PARENT is deleted. > >Supposedly, some RDBMs handle the options of when the LAST child is >deleted, natively, by declaration in the constraint. > >Duane Lee - EGOVX wrote: > > > >>It sounds like you are referring to a RI (Referential Integrity) >>constraint and if so one of the options when the constraint is defined >>is CASCADE DELETE, i.e., delete the children rows then delete the >>parent row and this is available in Postgres. >> >>Is this what you were asking or did I mis-interpret your query? >> >>Duane >> >>-----Original Message----- >>From: Dennis Gearon [mailto:gearond@fireserve.net] >>Sent: Wednesday, May 26, 2004 3:25 PM >>To: pgsql-general@postgresql.org >>Subject: [GENERAL] One more time on ONE-TO-MANY >> >> >>CC me, I'm digesting this list. >> >>From: >> http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3 >>A quote: >> ' In addition *the database designer chooses* an action for delete: >> >> * It's /only possible/ to delete a row in the one-table when there a >> no more related many-rows. >> * When deleting a row the RDBMS >> <http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3#rdbms> >> /automatically/ deletes the related data in the many table. This >> is called a /cascaded delete/. >> * When deleting the last 'many' the RDBMS /automatically/ deletes >> the related 'one' row.' >> >>I'm pretty sure that Postgres does not support the last one >>automatically. I shall have to do that one by either a chron script or a >>post trigger. >> >>Does anyone have experience with a database that will do the last one, >>and what database would that be? >> -- Thanks, Laura Vance Systems Engineer Winfree Academy Charter Schools, Data-Business Office 1711 W. Irving Blvd. Ste 310 Irving, Tx 75061 Web: www.winfreeacademy.com