I was not asking for SQL statements but that you think about what you are trying to achieve.
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, there is no "last row" in a set. Likewise ON DELETE CASCADE needs tob e triggered when the "last row" for a distinct (=UNIQUE) foreign key combination is deleted from the parent table. Again, there is no "last row" in a set. Not requiring UNIQUE means that FK enforcement relies on sets exhibiting an order, when they are clearly not allowed to have one by the relational model. Thus UNIQUE is required. (Proof by "reductio ad absurdum") SQL for non-unique foreign key ON DELETE CASCADE: -- assumes FK constraints are DEFERRED BEGIN; -- get the set of foreign key expressions touched by the delete CREATE TEMP TABLE TBD_C AS SELECT DISTINCT <FK> FROM P WHERE <delete condition>; -- remove the rows from parent table DELETE FROM P WHERE <delete-condition>; -- remove foreign key expressions still fulfilled by parent table DELETE FROM TBD_C WHERE EXISTS (SELECT 1 FROM P WHERE <FK-match>); -- remove "unparented" rows from child table DELETE FROM C WHERE <FK> IN (SELECT * FROM TBD_C); -- COMMIT; -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von James K. Lowden Gesendet: Dienstag, 28. Februar 2017 22:41 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] foreign key cardinality 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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
