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
