On Mon, Dec 15, 2008 at 11:24:30AM +0000, Nathan Catlow scratched on the wall:
> Can you tell me why you can even specify BIGINT to sqlite then? The world of SQL standard types is poor enough that SQLite makes best guesses for any reasonable type. In this case, anything with the string "int" is going to be integer. You can declare the column "MyVerySpecialInt" and it will be an INTEGER. By recognizing a wider array of types, SQLite is better able to digest SQL written for other systems. While this might be unsafe in other database systems, it isn't that big of a deal with SQLite, since SQLite's manifest typing means that if the SQL parser guesses wrong, it doesn't really matter. > But there is a difference, autoincrement ONLY works with "INTEGER", why? You're skipping over some important points there. AUTOINCREMENT does not work with INTEGER... AUTOINCREMENT only works with "INTEGER PRIMARY KEY" columns, and those columns have some very specific and very special meaning in SQLite-- mainly that they become a substitute ROWID column. You seem to be caught up on making a column, with a specific type of your choice, into a ROWID column just so you can utilize the AUTOINCREMENT feature. That strikes me as slightly backwards motivation. Merging a column into the ROWID column makes a deep and fundamental change to the table definition, changing the way the table is structured and stored. If you use it, you bring on number of fundamental changes in the behavior of the column. ROWID columns have a larger number of constraints and restrictions on them and don't act like "normal" SQLite columns. That's why the definition "INTEGER PRIMARY KEY" is *SO* specific... it triggers a rather meaningful change in the way the table and database operates, so the specific type declaration tries to protect against doing it on accident. "INTEGER" is a pretty good choice for this, since in traditional SQL it is a rather oddball type. If all you want is the ability to sequence a column, you can setup a trigger to do that for you on any column you want. For example, just copy the native ROWID back into your ID column, or whatever you're trying to make a BIGINT, or setup a sequence table or any number of other techniques. Yes, most database systems have some kind of support to do sequences and automatic assignments on arbitrary columns. For good or for bad, SQLite does not. (Mostly, I assume, because it is fairly straight forward to roll your own.) But most database systems don't let you alter the meaning of the ROWID column. If you have compatibility concerns, I'd be significantly more concerned about the ROWID aliasing than I would be with types. It is a much larger departure from other database systems. > I suppose if i wrote my own autoincrement all this would go away as I > could ensure its length. Yes, it seems like all of your problems could be solved by just defining your main column as BIGINT PRIMARY KEY (or whatever type will make all your support libs happy) and setting up your own sequence mechanism. This also has the advantage of keeping the ROWID column its own unique column, which is most likely what any kind of cross-platform SQL system is going to expect. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
