On Tue, 28 Feb 2017 17:13:30 +0000
Hick Gunter <h...@scigames.at> wrote:
> 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.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
