"Shane Harrelson" <[EMAIL PROTECTED]> wrote: > > I have two tables, an "Objects" table with a foreign key into a second > "Strings" table which is composed of unique values. It is a many to > one relationship, that is, several Objects may reference the same > String. When an Object is added, its associated String is added to > the Strings table. If the String already exists in the Strings > table, I'd like the new Object to reference the existing copy. > > Currently, I've implemented it as so (leaving out error handling, etc.): > > begin transaction > insert into Strings (value) VALUES ( 'foo') > if string insert result is SQLITE_OK > get rowid of last insert (sqlite3_last_insert_rowid) > else if result is SQLITE_CONSTRAINT > select rowid from Strings where value = 'foo' > end if > if rowid > insert into Objects (string_id) VALUES (rowid) > end if > if no error > commit transaction > else > rollback transaction > end if > > With my dataset, there is about a 10% chance of the string being a > duplicate -- that is about 1 in 10 string inserts hit the UNIQUE > constraint violation. > > I've tested "viloating" the internals of the VBDE and pulling the > rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT > result is returned and it is measurably (5-10%) faster then doing the > subsequent SELECT. >
What you are doing is the most efficient way that I can think of right off hand. If "violating" the internals is something you want to do that's fine - just remember that it is likely to break in irrepaiable ways in some future point release. No tears. Notice that an sqlite3_last_constraint_rowid() function doesn't really work because an insert might fail due to multiple constraint violations all on different rows. In your schema, perhaps, there can be no more than one constraint violated at a time, but it is easy enough to construct a schema where multiple rows can violate a constraint, so the sqlite3_last_constraint_rowid() idea does not generalize well. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
