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

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- -convenience / end-user nuisance" list of SQL features. Received on Wed Sep 30 2009 - 00:30:16 CEST