On 10/06/2015 02:45 AM, Andrew Cunningham wrote: >> Ok. My first guess is that you are missing the "suggested index" on the >> child table. Without this, if there is a foreign key violation in the >> db, each insert on the parent table will cause a linear scan of the >> entire child table. With the index, it's just a simple seek. >> >> https://www.sqlite.org/foreignkeys.html#fk_indexes >> >> If you're unsure, open your database using the command line tool and >> dump the schema using the ".schema" command. Post that here and someone >> should be able to tell you if you're missing an index or not. >> > > OK..... > > D:\ODB\odb-examples-2.4.0\relationship>D:\ODB\sqlite3.exe test.db > SQLite version 3.8.11.1 2015-07-29 20:00:57 > Enter ".help" for usage hints. > sqlite> .schema > CREATE TABLE "relation_employer" ( > "name" TEXT NOT NULL PRIMARY KEY); > CREATE TABLE "relation_project" ( > "name" TEXT NOT NULL PRIMARY KEY); > CREATE TABLE "relation_employee" ( > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > "first" TEXT NOT NULL, > "last" TEXT NOT NULL, > "employer" TEXT NOT NULL, > CONSTRAINT "employer_fk" > FOREIGN KEY ("employer") > REFERENCES "relation_employer" ("name") > DEFERRABLE INITIALLY DEFERRED); > CREATE TABLE "relation_employee_projects" ( > "object_id" INTEGER NOT NULL, > "value" TEXT NOT NULL, > CONSTRAINT "object_id_fk" > FOREIGN KEY ("object_id") > REFERENCES "relation_employee" ("id") > ON DELETE CASCADE, > CONSTRAINT "value_fk" > FOREIGN KEY ("value") > REFERENCES "relation_project" ("name") > DEFERRABLE INITIALLY DEFERRED); > CREATE INDEX "relation_employee_projects_object_id_i" > ON "relation_employee_projects" ("object_id");
I think you need indexes on the child key columns that don't already have them. i.e. CREATE INDEX relation_employee_projects_value_i ON relation_employee_projects_value(value); CREATE INDEX relation_employee_employer_i ON relation_employee(employer); Dan.
