On Wed, 1 Mar 2017 07:26:31 +0000
Hick Gunter <h...@scigames.at> wrote:
> Without a UNIQUE constraint there can be more than one row in the
> parent table that is the "parent row" of at least one row of the
> child table. To delete rows from the parent while still satisfying
> the EXISTS relation, you can delete all rows except the "last one"
> for each distinct (=UNIQUE) foreign key combination in the child
> table. This is not a set operation
First, I don't recognize "parent" and "child" as relational concepts.
It's convenient shorthand for us, but we must not let that terminology
contaminate our thinking.
You seem to think M:N represents some kind of logical challenge that
1:N does not. Why? Many databases have a rule similar to "every order
has line items, and every line item has an order". On deletion of the
last "child", delete the "parent". It is not the difference between
many and one that matters; it's the difference between any and none.
In an M:N relationship, cascading delete probably doesn't make sense.
But it wouldn't make more sense represented as three tables instead of
two!
Please consider my example again:
P(A,B) and C(C,B) where C(B) references A(B)
and A(B) is not unique
and
B(B), P(A,B), and C(C,B)
where P(B) and C(B) both reference B(B)
In neither case can you define a cascading relationship between P and
C. Adding B doesn't change that. It doesn't make the design more
"normalized".
What it does do is make it more "SQLized"; it allows the use of SQL FK
declarations to enforce that B(B) exists so long as P(B) or C(B) does.
By requiring a unique referent, though, SQL prevents declaration of the
rule, "for every C(B), there must be a P(B)".
You might well answer that the relationship should be
P(A,B) and C(C,A,B) where C(A,B) references A(A,B)
because, otherwise, what B do we mean? And that's effectively what I
did with the design that motivated my original question.
But I'm not convinced it's necessary. In my case, C(A) can be
derived from another relationship; C(B) adds information to that A and
cannot be related to some other A. The "which B" question can be
answered by a join. Unambiguously. I'm schlepping C(A) around only
for SQL reasons, not for any logical one I can see.
Probably I'm overlooking something, and probably this is not the right
forum. SQLite adheres to the SQL standard; that's established. Why
does the standard say what it does? I'm only saying it's not clear to
me that there's theoretical justification.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
