Skip site navigation (1) Skip section navigation (2)

Re: One more time on ONE-TO-MANY

From: Laura Vance <vancel(at)winfreeacademy(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: One more time on ONE-TO-MANY
Date: 2004-05-27 14:21:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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.
>-----Original Message-----
>From: Dennis Gearon [mailto:gearond(at)fireserve(dot)net]
>Sent: Wednesday, May 26, 2004 5:25 PM
>To: Duane Lee - EGOVX
>Cc: pgsql-general(at)postgresql(dot)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?
>>-----Original Message-----
>>From: Dennis Gearon [mailto:gearond(at)fireserve(dot)net]
>>Sent: Wednesday, May 26, 2004 3:25 PM
>>To: pgsql-general(at)postgresql(dot)org
>>Subject: [GENERAL] One more time on ONE-TO-MANY
>>CC me, I'm digesting this list.
>>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
>>      <>
>>      /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?

Laura Vance
Systems Engineer
Winfree Academy Charter Schools, Data-Business Office
1711 W. Irving Blvd. Ste 310
Irving, Tx  75061

In response to

pgsql-general by date

Next:From: btoberDate: 2004-05-27 15:22:06
Subject: Re: Naive schema questions
Previous:From: Jeff EckermannDate: 2004-05-27 13:48:43
Subject: Re: insert/update

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group