I'm creating a table of people, and I want to record a table of productions, with the amount each person contributed to that production. So we have a table of share splits; each production earns an amount of shares referenced by a splitid, and each split id covers a number of rows saying how many shares each person gets.
Why does the code fail when trying to add a production?
PRAGMA foreign_keys = ON;
CREATE TABLE people ( personid INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO "main"."people" ("name") VALUES ('Alice');
INSERT INTO "main"."people" ("name") VALUES ('Bob');
INSERT INTO "main"."people" ("name") VALUES ('Carol');
CREATE TABLE sharesplits ( splitid INTEGER NOT NULL, person INTEGER NOT NULL, numshares INTEGER NOT NULL, FOREIGN KEY(person) REFERENCES people(personid));
INSERT INTO "main"."sharesplits" ("splitid", "person", "numshares") VALUES (1, 1, 2); -- for splitid 1 Alice earns 2 shares
INSERT INTO "main"."sharesplits" ("splitid", "person", "numshares") VALUES (2, 1, 1); --\
INSERT INTO "main"."sharesplits" ("splitid", "person", "numshares") VALUES (2, 2, 1); --- for splitid 2 Alice, Bob and Carol all earn 1 share each
INSERT INTO "main"."sharesplits" ("splitid", "person", "numshares") VALUES (2, 3, 1); --/
CREATE TABLE productions (id INTEGER PRIMARY KEY, sharesearned INTEGER NOT NULL, FOREIGN KEY(sharesearned) REFERENCES sharesplits(splitid));
INSERT INTO "main"."productions" ("id", "sharesearned") VALUES (1, 1);
-- ^ FAILS With Result: foreign key mismatch - "productions" referencing "sharesplits"
SQLite will ignore the constraints on foreign keys by default; hence PRAGMA foreign_keys = ON;.
According to the documentation, the target of a foreign key must be a primary key or have a unique index on exactly those columns.
Based on your current schema, I don't think there's a simple way to fix this.
One suggestion: create a splitids table with just one primary key column and use that in both sharesplits and productions.