Question
I want to create a thread but only if the subject is unique in that section. Then I want to get the row id of the thread. How do I write it safely? My thoughts were something like
connection.Query<long>(@"insert into thread(section, subject, body)
select @section,@subject,@body
where not exists in (select 1 from thread where section=@section and subject=@subject;
select last_insert_rowid()", new {...}).First();
The problem is I have no idea if last_insert_rowid something from the past or from my insert statement. How do I write this query safely
Popular Answer
If understand correctly you can use OR IGNORE syntax. In order for it to work you have to have a UNIQUE constraint on section and subject
CREATE TABLE thread
(id INTEGER PRIMARY KEY,
section INT,
subject TEXT(128),
body TEXT(256),
UNIQUE (section, subject));
Now to ignore a row if it violates one of constraints
INSERT OR IGNORE INTO thread (section, subject, body)
VALUES (1, 'Subject1', 'Body1');
Now if insert succeeded then LAST_INSERT_ROWID() will return an id for the inserted row, but if it was a duplicate for section and subject insert failed it will return 0.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why