What should I consider when selecting a data type for my primary key?

Question

When I am creating a new database table, what factors should I take into account for selecting the primary key's data type?

Answer 1

If using a numeric key, make sure the datatype is giong to be large enough to hold the number of rows you might expect the table to grow to.

If using a guid, does the extra space needed to store the guid need to be considered? Will coding against guid PKs be a pain for developers or users of the application.

If using composite keys, are you sure that the combined columns will always be unique?

Answer 2

I usually always use an integer, but here's an interesting perspective.

http://www.codinghorror.com/blog/archives/000817.html

Answer 3

I'm partial to using an generated integer key. If you expect the database to grow very large, you can go with bigint.

Some people like to use guids. The pro there is that you can merge multiple instances of the database without altering any keys but the con is that performance can be affected.

Answer 4

For a "natural" key, whatever datatype suits the column(s). Artifical (surrogate) keys are usually integers.

Answer 5

It all depends.

a) Are you fine having unique sequential numeric numbers as your primary key? If yes, then selecting UniqueIdentifier as your primary key will suffice. b) If your business demand is such that you need to have alpha numeric primary key, then you got to go for varchar or nvarchar.

These are the two options I could think of.

Answer 6

In most cases I use an identity int primary key, unless the scenario requires a lot of replication, in which case I may opt for a GUID.

I (almost) never used meaningful keys.

Answer 7

Whenever possible, try to use a primary key that is a natural key. For instance, if I had a table where I logged one record every day, the logdate would be a good primary key. Otherwise, if there is no natural key, just use int. If you think you will use more than 2 billion rows, use a bigint. Some people like to use GUIDs, which works well, as they are unique, and you will never run out of space. However, they are needlessly long, and hard to type in if you are just doing adhoc queries.

Answer 8

Do not use a floating point numeric type, since floating point numbers cannot be properly compared for equality.

Answer 9

Hi Aaron

• Where do you generate it? Incrementing number's don't fit well for keys generated by the client.
• Do you want a data-dependent or independent key (sometimes you could use an ID from business data, can't say if this is always useful or not)?
• How well can this type be indexed by your DB?

I have used uniqueidentifiers (GUIDs) or incrementing integers so far.

Cheers Matthias

Answer 10

A great factor is how much data you're going to store. I work for a web analytics company, and we have LOADS of data. So a GUID primary key on our pageviews table would kill us, due to the size.

A rule of thumb: For high performance, you should be able to store your entire index in memory. Guids could easily break this!

Answer 11

Kim Trip goes in depth on indexing strategies in this podcast.

Answer 12

I don't really like what they teach in school, that is using a 'natural key' (for example ISBN on a bookdatabase) or even having a primary key made up off 2 or more fields. I would never do that. So here's my little advice:

• Always have one dedicated column in every table for your primary key.
• They all should have the same colomn name across all tables, i.e. "ID" or "GUID"
• Use GUIDs when you can (if you don't need performance), otherwise incrementing INTs

EDIT:
Okay, I think I need to explain my choices a little bit.

• Having a dedicated column namend the same across all table for you primary key, just makes your SQL-Statements a lot of easier to construct and easier for someone else (who might not be familiar with your database layout) easier to understand. Especially when you're doing lots of JOINS and things like that. You won't need to look up what's the primary key for a specific table, you already know, because it's the same everywhere.

• GUIDs vs. INTs doesn't really matters that much most of the time. Unless you hit the performance cap of GUIDs or doing database merges, you won't have major issues with one or another. BUT there's a reason I prefer GUIDs. The global uniqueness of GUIDs might always come in handy some day. Maybe you don't see a need for it now, but things like, synchronizing parts of the database to a laptop / cell phone or even finding datarecords without needing to know which table they're in, are great examples of the advantages GUIDs can provide. An Integer only identifies a record within the context of one table, whereas a GUID identifies a record everywhere.

Answer 13

Numbers that have meaning in the real world are usually a bad idea, because every so often the real world changes the rules about how those numbers are used, in particular to allow duplicates, and then you've got a real mess on your hands.

Answer 14

Use natural keys when they can be trusted. Some sources of natural keys can't be trusted. Years ago, the Social Security Administration used to occasionally mess up an assign the same SSN to two different people. Theyv'e probably fixed that by now.

You can probably trust VINs for vehicles, and ISBNs for books (but not for pamphlets, which may not have an ISBN).

If you use natural keys, the natural key will determine the datatype.

If you can't trust any natural keys, create a synthetic key. I prefer integers for this purpose. Leave enough room for reasonable expansion.

Answer 15

Unless you have an ultra-convenient natural key available, always use a synthetic (a.k.a. surrogate) key of a numeric type. Even if you do have a natural key available, you might want to consider using a synthetic key anyway and placing an additional unique index on your natural key. Consider what happened to higher-ed databases that used social security numbers as PKs when federal law changed, the costs of changing over to synthetic keys were enormous.

Also, I have to disagree with the practice of naming every primary key the same, e.g. "id". This makes queries harder to understand, not easier. Primary keys should be named after the table. For example employee.employee_id, affiliate.affiliate_id, user.user_id, and so on.

Answer 16

I usually go with a GUID column primary key for all tables (rowguid in mssql). What could be natural keys I make unique constraints. A typical example would be a produkt identification number that the user have to make up and ensure that is unique. If I need a sequence, like in a invoice i build a table to keep a lastnumber and a stored procedure to ensure serialized access. Or a Sequence in Oracle :-) I hate the "social security number" sample for natural keys as that number will never be alway awailable in a registration process. Resulting in a need for a scheme to generate dummy numbers.