Are there other strategies available for auto-incrementing a primary key besides the default x+1?

Question

Are there other strategies available for auto-incrementing a primary key? So instead of the usual x+n where n is 1, could n be some other number such as 5? Or perhaps a random number but such that the next ID is still unique?

I'm not sure what the uses would be, or even if it's a good idea, I'm just curious.

Answer 1

Depends on the RDBMS you are using. In oracle it would be this (provided you set a trigger up for the primary key):

SQL> create sequence fun_seq
     start with 8
     increment by 2;

Answer 2

In the case of Master-Master replication in MySQL, it is important to have the different servers have completely separate auto increments, to avoid collisions.

The keys are kept separate by specifying an offset and increment for each server. For example,

auto_increment_increment= 2
auto_increment_offset   = 2

Answer 3

In T-SQL (MS SQL Server) you use the IDENTITY property:

"IDENTITY [(seed, increment )]

seed Is the value that is used for the very first row loaded into the table.

increment Is the incremental value that is added to the identity value of the previous row that was loaded."

One thing you really shouldn't do is try and roll your own unless you really know what you are doing and can handle concurrency etc.

Edit: Why you need to be careful:

Here's some pseudo SQL that shows the danger of using your own increment etc:

1. @NextID = SELECT MAX(ID) FROM MyTable;

2. INSERT INTO MyTable(ID + 1, Name) VALUES (@NextID, "Dan");

Now, in most cases this would work OK. However, in a high volume database there is always a chance that another insert statement might occur between steps 1 and 2. You'd then end up with a primary key violation and an error. It may not happen often, but when it does it would be a pain to pinpoint.

The way around this is to have a LOCK before step 1 and release it after step 2. This effectively queues alls transactions and the database runs in "single user" mode. In high volume systems this can be very detrimental to performance.

This is why all major databases have sequences or built-in methods for auto-incremementing primary keys.

Answer 4

We also use different increment schemes for master/master replication situations. I think we have one server counting up and another counting down. Even/odd is perfectly valid, too.

Answer 5

For random ids (and ones you can make unique across servers), you can use GUID fields instead of int fields. These have problems and issues of their own and you should read about them in depth before committing to such a design however.