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

On Sep 29, 12:36*pm, cm <cmonthe... (AT) yahoo (DOT) com> wrote:
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?

On Sep 29, 10:36*am, cm <cmonthe... (AT) yahoo (DOT) com> wrote:
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

TroyK wrote:
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.

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

On Sep 29, 12:36*pm, cm <cmonthe... (AT) yahoo (DOT) com> wrote:
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.

Kevin Kirkpatrick wrote:

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.

On Sep 29, 12:31*pm, cm <cmonthe... (AT) yahoo (DOT) com> wrote:
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

On Sep 29, 6:30*pm, Kevin Kirkpatrick <kvnkrkpt... (AT) gmail (DOT) com> wrote:
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.