Re: One more time on ONE-TO-MANY

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(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?
>>
>>Duane
>>
>>-----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.
>>
>>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