INTEGER PRIMARY KEY question

There was a discussion which I should have followed more closely than I did.

I extensively use an auto-increment primary key field in my SQLite databases. Seems like someone said there was a potential problem with doing this.

Is this something I should be aware of?

a unique primary key is what is important. using an integer is fine. the only time it could be a problem is when there is lots and lots of rows.

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.

I use integer PKs all the time in SQLite databases.

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.

I use AR all the time too. and use dual PKs… and INT based one for AR to use. and an UUID based one that I use. I would love the day that AR uses UUID over INT but I can work around it until then.

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. :slight_smile:

I dont. :frowning: but I am willing to do research on the databases to make sure they can support what you need to make it so. which DBs dont support it (that you know of).

sb

These, I believe, are what I remember from the previous thread…

Thanks guys.

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.

Right, important distinction. So you really want to define an explicit Integer primary key. Otherwise your ROWID could change on you. Not sure why SQLite thought that was a good idea.

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

Thus, using ROWID is a bad idea. For multiple reasons.

just make sure it never exceeds 9223372036854775807 (even with only ONE record)
(max value of INT64)

not defining an integer primary key is the bad idea and then using rowid as one is what gets you in trouble

if you do define one then “rowid” and your defined one are aliases for each other

[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)

I was talking about AUTOINCREMENTED INTEGER PRIMARY KEY, which can only be an INTEGER as far as I know
A non-AutoInc Primary key can be anything you want (as long as unique)