![]() ![]() Sqlite> CREATE TABLE parent (a INTEGER PRIMARY KEY, b INTEGER, c, UNIQUE (a, b)) INSERT INTO parent (b) VALUES ('987654321') RETURNING a Now that I know that FOREIGN KEY just works, I've found the source of the original problem: sqlite> PRAGMA foreign_keys = ON ĬREATE TABLE parent (a INTEGER PRIMARY KEY, b, c, UNIQUE (a, b)) ĬREATE TABLE child (d INTEGER PRIMARY KEY, e, f, FOREIGN KEY (d, e) REFERENCES parent(b, a)) And the reason was I've switched to working on a semi equivalent example instead of the original point of the problem and made errors in translation. Change it to INSERT INTO child (e, f, g) VALUES (1, 1, 3) and it works.Īw, thank you! It seems I've just needed a second pair of eyes. Your inserts are failing because there is no row (a, b) with values (1, 2).The purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. INTEGER PRIMARY KEY is enough to guarantee automatically generated values. So I guess I just need assurance that this is and will be impossible by design. Of course, I could have created a composite primary key in the parent, but then I wouldn't be able to use AUTOINCREMENT. It's mentioned that parent and child keys must have the same cardinality in foreign key documentation although with all the examples I still kinda hoped that as I can include a unique constraint on a composition of primary key and column I can make it work. Sqlite> CREATE UNIQUE INDEX i ON parent(a, b) Sqlite> INSERT INTO child (e, f, g) VALUES (1, 2, 3) Sqlite> INSERT INTO parent (b, c) VALUES (1, 2) RETURNING a Sqlite> CREATE TABLE child(d INTEGER PRIMARY KEY AUTOINCREMENT, e, f, g, FOREIGN KEY (e, f) REFERENCES parent(a, b)) ![]() Sqlite> CREATE TABLE parent(a INTEGER PRIMARY KEY AUTOINCREMENT, b, c, UNIQUE (a, b)) I'm a bit disappointed: sqlite> PRAGMA foreign_keys = ON ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |