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
