Okay, here's a few newbie questions.
When dealing with tables with foreign constraints, how can the value of a
recently inserted primary key be propagated to multiple child tables? Is
there a way to save the value of last_insert_rowid() as a SQL statement?
Lastly, shouldn't the last INSERT statement below fail given that a row in
the parent table doesn't exist with a parent_id = 2?
$ cat create_tables.sql
PRAGMA foreign_keys = ON;
CREATE TABLE parent(
parent_id INTEGER PRIMARY KEY,
value INTEGER
);
CREATE TABLE child0(
value0 INTEGER,
child0_id INTEGER,
FOREIGN KEY(child0_id) REFERENCES parent(parent_id)
);
CREATE TABLE child1(
value1 INTEGER,
child1_id INTEGER,
FOREIGN KEY(child1_id) REFERENCES parent(parent_id)
);
$ sqlite3 foo.db '.read create_tables.sql'
$ sqlite3 foo.db
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> insert into parent (value) values (1);
sqlite> insert into child0 (value0, child0_id) values (2,
last_insert_rowid());
sqlite> insert into child0 (value0, child0_id) values (3,
last_insert_rowid());
sqlite> insert into child1 (value1, child1_id) values (4,
last_insert_rowid());
sqlite> .header on
sqlite> select rowid, * from parent;
parent_id|parent_id|value
1|1|1
sqlite> select rowid, * from child0;
rowid|value0|child0_id
1|2|1
2|3|1
sqlite> select rowid, * from child1;
rowid|value1|child1_id
1|4|2
$
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
