[ACCEPTED]-Reasons not to use an auto-incrementing number for a primary key-primary-key

Accepted answer
Score: 27

An auto generated ID can cause problems 6 in situations where you are using replication 5 (as I'm sure the techniques you've found 4 can!). In these cases, I generally opt for 3 a GUID.

If you are not likely to use replication, then 2 an auto-incrementing PK will most likely 1 work just fine.

Score: 20

There's nothing inherently wrong with using 39 AutoNumber, but there are a few reasons 38 not to do it. Still, rolling your own solution 37 isn't the best idea, as dacracot mentioned. Let 36 me explain.

The first reason not to use AutoNumber 35 on each table is you may end up merging 34 records from multiple tables. Say you have 33 a Sales Order table and some other kind 32 of order table, and you decide to pull out 31 some common data and use multiple table 30 inheritance. It's nice to have primary 29 keys that are globally unique. This is 28 similar to what bobwienholt said about merging 27 databases, but it can happen within a database.

Second, other 26 databases don't use this paradigm, and other 25 paradigms such as Oracle's sequences are 24 way better. Fortunately, it's possible 23 to mimic Oracle sequences using SQL Server. One 22 way to do this is to create a single AutoNumber 21 table for your entire database, called MainSequence, or 20 whatever. No other table in the database 19 will use autonumber, but anyone that needs 18 a primary key generated automatically will 17 use MainSequence to get it. This way, you 16 get all of the built in performance, locking, thread-safety, etc. that 15 dacracot was talking about without having 14 to build it yourself.

Another option is 13 using GUIDs for primary keys, but I don't 12 recommend that because even if you are sure 11 a human (even a developer) is never going 10 to read them, someone probably will, and 9 it's hard. And more importantly, things 8 implicitly cast to ints very easily in T-SQL 7 but can have a lot of trouble implicitly 6 casting to a GUID. Basically, they are 5 inconvenient.

In building a new system, I'd 4 recommend using a dedicated table for primary 3 key generation (just like Oracle sequences). For 2 an existing database, I wouldn't go out 1 of my way to change it.

Score: 10

from CodingHorror:

GUID Pros

  • Unique across every table, every database, every server
  • Allows easy merging of records from different databases
  • Allows easy distribution of databases across multiple servers
  • You can generate IDs anywhere, instead of having to roundtrip to the database
  • Most replication scenarios require GUID columns anyway

GUID Cons

  • It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
  • Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
  • The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes

The article provides 3 a lot of good external links on making the 2 decision on GUID vs. Auto Increment. If 1 I can, I go with GUID.

Score: 6

The procedure method of incrementing must 5 be thread safe. If not, you may not get 4 unique numbers. Also, it must be fast, otherwise 3 it will be an application bottleneck. The 2 built in functions have already taken these 1 two factors into account.

Score: 6

It's useful for clients to be able to pre-allocate 5 a whole bunch of IDs to do a bulk insert 4 without having to then update their local 3 objects with the inserted IDs. Then there's 2 the whole replication issue, as mentioned 1 by Galwegian.

Score: 5

Here's the thing with auto incrementing 10 integers as keys:

You HAVE to have posted 9 the record before you get access to it. That 8 means that until you have posted the record, you 7 cannot, for example, prepare related records 6 that will be stored in another table, or 5 any one of a lot of other possible reasons 4 why it might be useful to have access to 3 the new record's unique id, before posting 2 it.

The above is my deciding factor, whether 1 to go with one method, or the other.

Score: 4

My main issue with auto-incrementing keys 11 is that they lack any meaning

That's a requirement of a primary key, in my 10 mind -- to have no other reason to exist 9 other than identifying a record. If it 8 has no real-world meaning, then it has no 7 real-world reason to change. You don't 6 want primary keys to change, generally speaking, because 5 you have to search-replace your whole database 4 or worse. I have been surprised at the 3 sorts of things I have assumed would be 2 unique and unchanging that have not turned 1 out to be years later.

Score: 3

Using a unique identifiers would allow you 8 to merge data from two different databases.

Maybe 7 you have an application that collects data 6 in multiple database and then "syncs" with 5 a master database at various times in the 4 day. You wouldn't have to worry about primary 3 key collisions in this scenario.

Or, possibly, you 2 might want to know what a record's ID will 1 be before you actually create it.

Score: 2

One benefit is that it can allow the database/SQL 2 to be more cross-platform. The SQL can 1 be exactly the same on SQL Server, Oracle, etc...

Score: 1

The only reason I can think of is that the 2 code was written before sequences were invented and 1 the code forgot to catch up ;)

Score: 1

I would prefer to use a GUID for most of 17 the scenarios in which the post's current 16 method makes any sense to me (replication 15 being a possible one). If replication was 14 the issue, such a stored procedure would 13 have to be aware of the other server which 12 would have to be linked to ensure key uniqueness, which 11 would make it very brittle and probably 10 a poor way of doing this.
One situation 9 where I use integer primary keys that are 8 NOT auto-incrementing identities is the 7 case of rarely-changed lookup tables that 6 enforce foreign key constraints, that will 5 have a corresponding enum in the data-consuming 4 application. In that scenario, I want to 3 ensure the enum mapping will be correct 2 between development and deployment, especially 1 if there will be multiple prod servers.

Score: 1

Another potential reason is that you deliberately 5 want random keys. This can be desirable 4 if, say, you don't want nosey browsers leafing 3 through every item you have in the database, but 2 it's not critical enough to warrant actual 1 authentication security measures.

Score: 1

My main issue with auto-incrementing keys 3 is that they lack any meaning.

For tables where certain fields provide uniqueness (whether 2 alone or in combination with another), I'd 1 opt for using that instead.

Score: 0

A useful side benefit of using a GUID primary 12 key instead of an auto-incrementing one 11 is that you can assign the PK value for 10 a new row on the client side (in fact you 9 have to do this in a replication scenario), sparing 8 you the hassle of retrieving the PK of the 7 row you just added on the server.

One of 6 the downsides of a GUID PK is that joins 5 on a GUID field are slower (unless this 4 has changed recently). Another upside of 3 using GUIDs is that it's fun to try and 2 explain to a non-technical manager why a 1 GUID collision is rather unlikely.

Score: 0

Galwegian's answer is not necessarily true.

With 10 MySQL you can set a key offset for each 9 database instance. If you combine this with 8 a large enough increment it will for fine. I'm 7 sure other vendors would have some sort 6 of similar settings.

Lets say we have 2 databases 5 we want to replicate. We can set it up in 4 the following way.

increment = 2
db1 - offset = 1
db2 - offset = 2

This means that

db1 will 3 have keys 1, 3, 5, 7....

db2 will have keys 2 2, 4, 6, 8....

Therefore we will not have 1 key clashes on inserts.

More Related questions