On 05/14/2012 10:39 AM, Jonas Malaco Filho wrote:
I found this on the docs <http://www.sqlite.org/foreignkeys.html>:
If this SELECT returns any rows at all, then SQLite concludes that
deleting the row from the parent table would violate the foreign key
constraint and returns an error. Similar queries may be run if the
content of the parent key is modified *or a new row is inserted into the
parent table*. If these queries cannot use an index, they are forced to
do a linear scan of the entire child table. In a non-trivial database,
this may be prohibitively expensive.
I have to ask: _why would similar queries on the child table be run upon
insertions into the parent table_?
Fair question. Note the "may". They're not always run. Here's an
example:
CREATE TABLE p(x PRIMARY KEY);
CREATE TABLE c(y REFERENCES p(x) DEFERRABLE INITIALLY DEFERRED);
BEGIN;
INSERT INTO c VALUES('abc');
INSERT INTO p VALUES('abc');
COMMIT;
When the first insert is run, SQLite records the fact that there is
a foreign key violation in the database (since there is no parent
key for 'abc'). When the second insert is run, SQLite checks if it
resolves the existing foreign key violation by checking for rows
in "c" that match the new parent key. i.e. it internally does:
SELECT count(*) FROM c WHERE y='abc';
However, if you were to do this:
BEGIN;
INSERT INTO p VALUES('def');
COMMIT;
Then SQLite would not have to scan table "c" - as it knows that there
are no FK violations in the database and so no reason to check if
any have been resolved.
Quite probably this thread should be on sqlite-users@sqlite.org
instead of this list. Since it is of interest to users, not just
those working on the development of SQLite.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
