> > Let's assume you have a parent table P (color, shape, style, ...)
> > that is unique on the three named fields. Let's assume you have a
> > child table C (id, ...,color, shape, ...) that references P
> > (color,shape)
>
> As far as SQL goes, Kees provided the relevant text, so we know SQLite
> conforms to the standard in this regard. Thank you, Kees.
>
> To answer your questions, supposing UNIQUE were not required for a
> FK referent:
>
> > Which, if any, of the P rows can you safely delete without losing
> > referential integrity?
>
> delete from P where not exists (
> select 1 from C where color = P.color and shape = P.shape );
>
> > Assuming ON DELETE CASCADE, when do you delete the rows from C?
>
> delete from C where exists (
> select 1 from P where color = C.color and shape = C.shape
> and /* ... P criteria ... */
> );
>
> From a theoretical standpoint, for relations
>
> P{A,B}
> and
> C{C,B} with C{B} referencing P{B}
>
> without loss of information we may add
>
> B{B}
>
> and constraints
>
> P{B} references B{B} and C{B} references B{B}
>
> But, having added B, we have not added any information. We have
> merely added another relation that is the projection of P{B}.
>
> It is *usually* true that B will have other attributes, in which case
> of course a B table would be needed to hold them. Maybe that, or
> practical considerations, or both, motivated the SQL rule.
It has always been the case in SQL (and relational databases in general) that
Parents must be unique. It was even the case in Hierarchical databases that
Parents of a Set must be unique -- that is, identify a 1:N relationship. N:M
relationship modelling requires a connecting N:M table where the relationships
between table A -> C <- B are one parent in A identifies N records in C, each
of which identifies one record in B (that is, A -> C is 1:N and B -> C is 1:M
with the resulting A <-> B relationship being N:M). Only Network Model (I
forget whether it has to be Network Extended -- it has been a long time since I
used one) Hierarchical databases model N:M sets directly.
It has always been a requirement that FK relationships are 1:N mappings,
otherwise update anomalies will occur. If you have a relational database that
does not "naturally" have all FK relationships as 1:N, then you have not
normalized the data properly (or sufficiently).
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
