I am not sure what the problem would be… other then some people consider it overhead. For example, if you don’t already have something for each entry that could be considered a unique identifier to key on already.
If you are ever going to synchronies data between databases then it’s much better to use UUID’s which, theoretically, will never repeat among your rows in any database (even if new data is created in other copies). This makes it easy to query any number of copies of a database and say, “do you have this record?” Whereas if all you use is integer primary keys a record 111 will most likely exist on all of them and none of them will be the record you really want.
Because we like ActiveRecord, in instances like this, we have the integer primary key and a UUID field. It’s redundant but it solves the problem. Some day we’ll convert ActiveRecord over to use UUID primary keys. But that might be for certain databases that handle them properly. Haven’t given it much thought yet.
It’s really a matter of supporting it in all databases. The integer primary key requires no work on our part. AFAIK, it’s not an automatic thing in some of the databases. If you know different I’m willing to talk.
Most database(s) support ROWID, with SQLITE if you specify your own “Integer AutoIncrement PK” then in reality it maps itself to the ROWID (unless you specifically created the table WITHOUT ROWID), then it adds some overhead. PK by definition is unique…
Relying on ROWID in SQLite isn’t a good idea since doing things like VACCUM might change the value. If you define a field as Auto increment primary key it will never change even if the underlying ROWID does get changed in the vacuum.
Hence the extra overhead I mentioned, as it maintains a Sequence Table to handle that value.
And AutoIncrement applies only to INTEGER Primary Key, not to just “any” Integer value, and seems to be the only defined field in an SQLite database that enforces the datatype.
ROWIDS can change IF and ONLY IF you DO NOT define an integer primary key of your own and rely on the default rowid that SQLIte will associate with every row
IF you define your own they won’t change using vacuum
From VACUUM The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.
If it really is your primary key you’d define it (well you should and any good DBA would beat you if you didn’t)
If not its a “convenience id for a row” I suppose
But far too many people dont explicitly define it, rely on it, and use it as an FK in other tables and then get surprised when it changes
[quote=326759:@Dave S]just make sure it never exceeds 9223372036854775807 (even with only ONE record)
(max value of INT64)[/quote]
Or that UUID’s are indeed UNIVERSALLY unique
A few billion rows and one or two collisions can cause a lot of work and real trouble (says one of the guys who had to figure out wtf was going on and then fix the mess)