What happens when DB engine runs out of numbers to use for primary keys?

Question

Since DBs do not reuse numbers of deleted records it is possible to run out of numbers, especially if you pick not really a big integer type for this column.
What would happen and how to prevent it if it's bad?

// SQL Server, MySQL //

Answer 1

For MySQL, it is documented that:

The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.

Answer 2

I think exactly what happens will be dependent on which database engine you're using (there may even be differences between INNODB and MyISAM in MySQL). Whatever happens, it's not going to be pretty.

You'd simply have to change the column type to a larger integer.

Answer 3

Yes it is possible: if you only allow for 2 digit numbers you can only have IDs up to 99, and so on. Inserts would fail once the limit was reached. It is a matter of common sense to choose an appropriate size.

Answer 4

You end up with a 3+ Hour Downtime, like Slashdot did on their Comments-Function.

Answer 5

In Postgres, the "serial" type is equivalent to the creation of a SEQUENCE with the NO CYCLE option, and setting the default of the field to nextval. Exhausting such a sequence produces an error:

http://www.postgresql.org/docs/8.3/interactive/sql-createsequence.html

Answer 6

Most database systems have a numeric datatype that can be wider than 32 bits. If you anticipate more than 2^32 records you should use an appropriate key width.

Answer 7

It depends on your database, I believe in MS SqlServer, you simply cannot insert any new rows until you fix the problem. The last time I encountered it, we fixed the problem by reseeding the identity column to 1. That's obviously not a universal fix, but it was ok with our situation.

Answer 8

Oracle doesn't support autoincrementing ID columns and standard practice is to use a sequence generator. A sequence generates integers of up to 28 digits, so if you run out of those then ... I guess you have a pretty big table. But behaviour would then be dependent on the configuration of the sequence generator -- either an error or it would cycle back to the start value and you'd get a PK constraint violation on the next insert.

Answer 9

I tried this in SQL 2000 some time ago. After Integer.MaxValue the next identity value is Integer.MinValue. It then keeps counting up as you would expect. As long as the records that used to exist at 1,2,3 etc have gone by the time it gets there nothing bad will happen. If it runs into a duplicate (and the field is the primary key) then the insert fails with a key violation. I haven't tried an identity column that isn't constrained to unique values though. I would guess it would be happy with the duplicates.

Answer 10

Generally you'll get an error. Use a BIGINT if you are paranoid.