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)
Now insert some rows into P (red,square,none, ...) and (red, square, bold, ...). And insert some rows into C (1,...,red, square, ...) and (2,...,red, square,...). Which, if any, of the P rows can you safely delete without losing referential integrity? Assuming ON DELETE CASCADE, when do you delete the rows from C? -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von James K. Lowden Gesendet: Dienstag, 28. Februar 2017 17:42 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] foreign key cardinality On Tue, 28 Feb 2017 08:48:02 +0000 Hick Gunter <h...@scigames.at> wrote: > "If they are not the primary key, then the parent key columns must be > collectively subject to a UNIQUE constraint or have a UNIQUE index." Thank you, Hick. I have always thought af a foreign key as an existence test. Looking around, I see that other implementations also often require the referent to be unique. I'm not convinced that's justified theoretically, but at the moment I can't check against my usual resources. I now see how to solve the conundrum I faced that motivated my complaint. It requires more columns and UNIQUE constraints than I think are strictly necessary, but it can be made to work. --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
