Re: deleting a row potentially referenced by many rows in another table
- From: Brian <brian@xxxxxxxxxxxxxxxxxxx>
- Date: Tue, 29 Sep 2009 21:12:38 -0700 (PDT)
On Sep 29, 6:30 pm, Kevin Kirkpatrick <kvnkrkpt...@xxxxxxxxx> wrote:
On Sep 29, 12:36 pm, cm <cmonthe...@xxxxxxxxx> 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 -
.
- References:
- Prev by Date: Re: Can relational alegbra perform bulk operations?
- Next by Date: Re: Entity and Identity
- Previous by thread: Re: deleting a row potentially referenced by many rows in another table
- Next by thread: Can relational alegbra perform bulk operations?
- Index(es):
Relevant Pages
|
Loading
