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