CODEWITHSUNDEEP
databaseprimary-key
z-boss
z-boss

Reputation: 17608

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

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 //

Upvotes: 8

Views: 6038

Answers (10)

bobflux
bobflux

Reputation: 11581

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

Upvotes: 0

pipTheGeek
pipTheGeek

Reputation: 2713

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.

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

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.

Upvotes: 0

Kevin
Kevin

Reputation: 7309

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.

Upvotes: 2

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66622

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.

Upvotes: 2

Martin v. Löwis
Martin v. Löwis

Reputation: 127477

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

Upvotes: 1

Michael Stum
Michael Stum

Reputation: 180964

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

Upvotes: 11

Tony Andrews
Tony Andrews

Reputation: 132600

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.

Upvotes: 1

Greg
Greg

Reputation: 321698

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.

Upvotes: 5

Martin Bøgelund
Martin Bøgelund

Reputation: 1680

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.

Upvotes: 2

Related Questions