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.
>From: Dennis Gearon [mailto:gearond(at)fireserve(dot)net]
>Sent: Wednesday, May 26, 2004 5:25 PM
>To: Duane Lee - EGOVX
>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?
>>From: Dennis Gearon [mailto:gearond(at)fireserve(dot)net]
>>Sent: Wednesday, May 26, 2004 3:25 PM
>>Subject: [GENERAL] One more time on ONE-TO-MANY
>>CC me, I'm digesting this list.
>> ' 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
>>Does anyone have experience with a database that will do the last one,
>>and what database would that be?
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: btober||Date: 2004-05-27 15:22:06|
|Subject: Re: Naive schema questions|
|Previous:||From: Jeff Eckermann||Date: 2004-05-27 13:48:43|
|Subject: Re: insert/update|