deleting a row potentially referenced by many rows in another table
cm
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
TroyK
What is the meaning of the presence of a row in Parent at first
insertion (i.e., before any "children" are entered) that is not the
same meaning later on after all of its children are deleted?
jefftyzzer
Yeah, off-hand all that comes to mind is a trigger that fires on
DELETEs and does an EXISTS test. Since your PK and, almost certainly,
FK columns are indexed, the performance may be acceptable. Which RDBMS
are you running?
--Jeff
paul c
>> 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
>
> What is the meaning of the presence of a row in Parent at first
> insertion (i.e., before any "children" are entered) that is not the
> same meaning later on after all of its children are deleted?
>
This isn't likely to do any good as far as CM's question is concerned,
but the question points out how limited the possible "meanings" can be
when most if not all dbms' fail to fully implement logical independence.
If they did support that, one could define the 'parent' and 'child'
(don't ask me why tree scheme terms are creeping in here) as projections
and then ask what should happen when deleting from their join.
cm
> Yeah, off-hand all that comes to mind is a trigger that fires on
> DELETEs and does an EXISTS test. Since your PK and, almost certainly,
> FK columns are indexed, the performance may be acceptable. Which RDBMS
> are you running?
>
> --Jeff
I am running MySQL. Yes, the trigger approach was suggested to me
separately, and probably could be made to work. I am wondering about
concurrency issues though. Suppose the EXISTS test indicates that no
FKs reference the applicable parent row when the check is made, but,
before the delete of the parent row can be made, another insert finds
the parent row already exists and references it.
Thanks,
Carl
Kevin Kirkpatrick
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.
Bob Badour
It falls into the category of probabilities of error. Referencing (a)
non-unique attribute(s) is far more often an error than the desired
reference. Having the compiler complain catches more errors earlier.
Since a FK reference is just a short-hand for a longer wff, one can
alway declare the more relaxed constraint using the wff.
jefftyzzer
I'm not familiar enough with mySQL to give you a definitive answer, so
you may want to post your latest question to that forum. What is
mySQL's concurrency model (for lack of a better word)?--is it more
traditional (lock list, isolation level, etc.) like, say, DB2, or is
it more like Oracle's (MVCC-like, i.e., writers never block readers,
no lock list, etc.)? Or, does the answer to that question depend on
which storage engine was implemented?
--Jeff
Brian
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 -