On 28 Jun 2012, at 9:05pm, Nico Williams <n...@cryptonector.com> wrote:
> I think AUTOINCREMENT should imply that the column values a) must be > INTEGER, b) tracking the max value seen so far. (b) is tricky because > it's tempting to not require an index on that column unless it's > constrained to be unique (implied for a primary key), but then, if > there is no index then ensuring that an autoincrement value is not > used requires a leap of faith -- but again, if not declared unique > then I think it's fair to assume that it isn't required to be unique. I think SQLite4 will need to support AUTOINCREMENT for INTEGERs because so many SQL users will assume that it's supported. But the use of AUTOINCREMENT for key fields is really something more like 'always be able to generate a unique value'. There's no reason, for instance, why it shouldn't work for a TEXT column or even a BLOB. So support for it can be presented like the max() function: find the highest value so far, and use it to generate one a little higher. You could even expose a genuine SQLite4 aggregate function for it, and insist that anyone who defines a new column type supply the function to be used to generate the next value. This will make life simpler for those working with relational databases, because it'll let them do BEGIN EXCLUSIVE; SELECT next_key_value(id) FROM authors; <-- store value here SELECT next_key_value(id) FROM books; <-- store value here -- now you already know all the values you need for the rest INSERT INTO authors (id,name) VALUES (***,'Zenna Henderson'); <-- use value here INSERT INTO authors (authorID,title) VALUES (***,'Pilgrimage'); <-- use value here INSERT INTO shortStories (bookID,title) VALUES (***,'Shadow on the Moon'); <-- use value here END; as an alternative to using last_insert_rowid() . Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users