On Mon, Dec 15, 2008 at 04:34:29PM +0000, Nathan Catlow wrote: > > Secondly, AUTOINCREMENT in SQLite only works on an INTEGER PRIMARY > > KEY, not on any other kind of primary key or on any non-primary-key > > field. BIGINT PRIMARY KEY is not an INTEGER PRIMARY KEY and so > > AUTOINCREMENT won't work on it. > > So you are saying there is an internal difference between INTEGER and > BIGINT in sqlite? I thought they were just synonyms for INTEGER. It > seems from my patch it is very simple to make autoincrement work for > BIGINT as to sqlite it's just another way of saying INTEGER. I could > be wrong here, I haven't examined the code close enough.
There is no difference between INTEGER and INT and BIGINT and SMALLINT. There is a difference between "INTEGER PRIMARY KEY" and all of the above. The reason is that there are two kinds of columns in SQLite3: - ROWID ROWID is always be a unique 64-bit integer. - all other columns These are always ducktyped with a preference derived from the declared type. and the thing is that "INTEGER PRIMARY KEY" is an alias for "ROWID," which means "INTEGER PRIMARY KEY" is different from "all other columns." This alias is baked into the parser. You could certainly bake in other aliases. You've been told is that that's not likely to happen. But I think you could make a stronger case: it's not that you are surprised here, but that other SQL implementations have BIGINT PRIMARY KEY with semantics that are compatible with INTEGER PRIMARY KEY (do they?) and for portability's sake it'd be nice for SQLite3 to provide BIGINT PRIMARY KEY as well. > I see no evidence to support the assertion that BIGINT PRIMARY KEY != > INTEGER PRIMARY KEY. I can JOIN tables on BIGINT and INTEGER because > sqlite regards them as the same INTEGER type, sqlite just doesn't > care. This is great, until it comes to autoincrementing then it's > unusually fussy. You've seen evidence: the maintainers have to you so, the documentation has told you so, and experience has told you so. Perhaps some SQL standard says that you can have BIGINT PRIMARY KEY, but unless it says implementations of SQL must provide BIGINT PRIMARY KEY then SQLite3 needn't actually provide it. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
