CODEWITHSUNDEEP
databasesqlite
jstm88
jstm88

Reputation: 3355

Inserting SQL Data into Linked (Foreign Key) Tables in SQLite

I have two tables linked by a foreign key. Example:

"CREATE TABLE one (id INTEGER PRIMARY KEY, data REAL, time TEXT NOT NULL DEFAULT (datetime('now')));"

"CREATE TABLE two (id INTEGER PRIMARY KEY, parent INTEGER, CONSTRAINT fc_two FOREIGN KEY (parent) REFERENCES one(id));"

So I'd like to do an INSERT INTO with an embedded JOIN, but I already tried (and then Googled it) and apparently it doesn't work. I did find a way to do it using something called @@Identity but that doesn't seem to work with SQLite. Basically, I need to:

  1. Insert data into one
  2. Find the value of "id" for the row I just inserted into one
  3. Insert data into two using the id value I just got from one

1 and 3 are easy, but to get 2 I would need to query for the one I just inserted. None of the data columns (except id) are unique, and the one unique combination (the set of all non-id columns as a whole is unique, just not any individual one) is impossible to reliably query against.

What's the best way to perform this operation?

Upvotes: 3

Views: 4147

Answers (1)

cement
cement

Reputation: 2905

SELECT last_insert_rowid()

Upvotes: 6

Related Questions