Your assumption does not correspond with the documentation, see http://sqlite.org/foreignkeys.html :
"The parent key is the column or set of columns in the parent table that the foreign key constraint refers to. This is normally, but not always, the primary key of the parent table. The parent key must be a named column or columns in the parent table, not the rowid." " 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." "Cardinality" refers to the number of fields in the constraint. As in "(x,y,z) of child table must match (a,b,c) of parent table" is valid, while "(x,y) of child must match (a) of parent table" is invalid, just like "(x,y) of chid table must match (a,b,c) of parent table". -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von James K. Lowden Gesendet: Montag, 27. Februar 2017 21:42 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] foreign key cardinality I would like to illustrate a problem with SQLite's foreign key enforcement policy. SQLite requires that foreign keys refer to primary keys. That rule has no relational foundation, and prevents the use of foreign keys that are perfectly valid. I have these tables (non-key columns omitted for brevity): Directory ( name primary key ) File ( name, dname references Directory(name), primary key (name, dname) ) Program ( name primary key ) Note that a filename is not unique except within a directory. Now I have authorization tables that constrain what directories and files a program can access, and in what order. ProgDir ( pname references Program(name), dname references Directory(name), ord, primary key (pname, ord) ) ProgFile ( pname, pord, fname, ord, primary key (pname, pord, fname), foreign key (pname, pord) references ProgDir (pname, ord), foreign key (fname) references File(name) ) -- ouch A program can access a directory and that directory's files. The file access order depends on which directory we're referring to, and requires that the program be permitted to use that directory. It's not necessary to carry dname in ProgFile; it can be derived from ProgDir.dname. But it would be nice to know that the ProgFile.fname exists in File.name. If I added Progfile.dname, so that I could declare a foreign key to File, it would be nice to also add it to the FK declaration referring to ProgDir: foreign key (pname, pord, dname) references ProgDir(pname, ord, dname) because that reflects the rule that permission to use a file requires permission to use the file's directory. In both cases, I'm stuck. SQLite requires a FK declaration to match the referrenced table's PK, else you get a "foreign key mismatch" error. (At least, that's my understanding of, "Parent and child keys must have the same cardinality." If ProgFile has no dname, it can't have a foreign key to File. If it does have dname, it can't include it in its reference to ProgDir. The relational model doesn't recognize the concept of "primary key", and the rule that a foreign key must refer to a primary key is not part of the SQL standard. The rule is unnecessary, and interferes with accurate foreign key reference declarations. --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
