Re: deleting a row potentially referenced by many rows in another table

On Sep 29, 6:30 pm, Kevin Kirkpatrick <kvnkrkpt..._at_gmail.com> wrote:
> On Sep 29, 12:36 pm, cm <cmonthe..._at_yahoo.com> wrote:
>
>
>
>
>
> > I have a situation that is somewhat the opposite of the case in which
> > you would use ON DELETE CASCADE on a foreign key in a child table to
> > ensure that when a row in the parent table was deleted, the delete
> > would cascade to the matching rows in the Child table (see example
> > below).
>
> > CREATE TABLE Parent (
> > parentId INT NOT NULL,
> > PRIMARY KEY(parentId)
> > );
>
> > CREATE TABLE Child (
> > childId INT NOT NULL,
> > PRIMARY KEY(childId),
> > FOREIGN KEY (parentId) REFERENCES Parent(parentId)
> > );
>
> > In my case, I want to delete a row in the parent table when the last
> > row in the child table referencing that (parent) row is deleted (N:1
> > from child to parent). Is there any way to do this automatically?
> > Otherwise, it seems that it would be necessary to check the Child
> > table each time a delete is performed to see if any rows still
> > reference the same parent row and then delete it (the parent row) if
> > none are found.
>
> > Thanks,
> > CM
>
> Well, if there were a logically identifiable child which should always
> be the first added and last removed, you could have a foreign key from
> the parent to the primary key of *that* child.  For instance, an
> invoice that has many lines, that will always have line 1, might use
> this approach (works in Oracle, not sure how it'd fare in other
> products)
>
> create table invoices
> (invoice_num number primary key,
> first_line number DEFAULT 1 check (first_line = 1)  not null)
> /
>
> create table lines (invoice_num number,
> constraint lines_fk1
>     foreign key (invoice_num)
>     references invoices(invoice_num)  on delete cascade
>     initially deferred deferrable ,
> line_no number not null,
> constraint lines_pk primary key (invoice_num, line_no))
> /
>
> alter table invoices add (constraint invoices_fk1
> foreign key (invoice_num, first_line)
> references lines(invoice_num, line_no)
>  on delete cascade initially deferred deferrable )
> /
>
> Of course, if you delete line1 prematurely, you'll start a cascade
> that wipes out the invoice itself, and all other lines on that
> invoice.
>
> I'm curious - is there any theory-based reason that most (all?)
> commercial DBMSs require a uniqueness on the parent of a foreign key
> constraint?  I wouldn't imagine - I've always put it on the "DBMS-
> implementer-convenience / end-user nuisance" list of SQL features.- Hide quoted text -

While I suspect that the primary reason is "DBMS-implementer- convenience," there is a theoretical justification for it. See the paper, "Justification for Inclusion Dependency Normal Form," by Levene and Millist. You can find a copy of it here:

http://eprints.bbk.ac.uk/196/1/Binder1.pdf

The authors present a strong case for IDNF which requires in addition to all relations being in BCNF that all inclusion dependencies be noncircular and key-based.

>
> - Show quoted text -
Received on Wed Sep 30 2009 - 06:12:38 CEST