Enforcing Foreign Keys
The SQLite documentation for ommited features
says:
FOREIGN KEY constraints are parsed but are not enforced. However,
the equivalent constraint enforcement can be achieved using triggers.
The above ommission is a little strange because the SQLite library has
contained code to parse and verify foreign key (FK) constraints ever
since SQLite version 2.5.0, released in June 2002. Support for triggers
dates from the same era.
This concept project takes the existing infrastructure and fills in
the missing bits. For SQLite version 2, the extra code is only 300
lines in a new file, and a few lines added to the core code.
The approach taken is to look at the parsed and checked foreign key
structures and automatically generate the triggers that enforce the
FK constraint. Generating the triggers is delayed until commit time, to
allow all tables to be created before the triggers are processed. For the
same reason, FK trigger processing is delayed till the end of initialisation
when the schema is (re-)read from the sqlite_master table. If
the master table does not exist, or is dropped at commit time, the FK
triggers are not created and the FK constraint is ignored (as in regular
SQLite).
|
| | |
Resources
Design
Source Tree (SQLite2 based)
Core source code
|
