[sqlite] sqlite-users Digest, Vol 94, Issue 4

[Andrew Cunningham]

>
> 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");