Possible Duplicate:
Why use an int as a lookup table's primary key?

So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think about decision making about primary key theories.

The professor in my university suggested the class to make primary keys from one or more fields which make up to one unique info about each column. And yes, I want to have a habit of applying natural keys instead of surrogate keys. On Wikipedia, the advantages and disadvantages of surrogate keys are listed, I strictly recommend This Article

I have seen people use integer ID fields for everything and nobody judges this method because

  • it "looks" efficient
  • a number field is used and it looks cooler because of its size per row in memory

I am beginning to think an extra ID field is just creating redundant data with no actual benefit. So why should I create an ID column when I can use other columns as key fields?

  • If your ID field is 32 bits, it's equivalent to 4 ASCII characters already.
  • If your Id field is 64 bit integer, it's 8 characters string so it doesn't actually save that much of memory (what implied here is the memory used on comparison. extra id column is already adding to the memory used (both HDD and RAM) )
  • An extra ID field doubles your indexing cost because you will also index a unique field you can use as a primary key.
  • You make extra joins if you need the data you could have used as a key field, for example, if you stored a unique user ID in one blog post, to show the name of the author, you make a join query, if your key field was the author's name, you don't need to join because you store the relevant data in the blog post table. a foreign key field with meaningful data reduces the need for subquery or join

enter image description here

  • Creating an extra id field "adds" to the memory load, it's not a replacement of a unique string field, you are not replacing a char-varchar field with an integer, you are adding an extra column and it creates extra data flow. so any comparison of data store should be done between "string" and "int+string". adding an integer id field does not save space.

on the other hand

  • assigning a primary key data which gets value from user input, can be problematic because people can enter, for say, their social security number wrong and the actual person who wants to register won't be able to register because of the unique policy. This can be circumvented by adding extra digit or digits to the original number.

Extra resources:

  1. Comparison of Natural vc Surrogate keys

My conclusion from reading articles is that I should use natural keys whenever possible instead of skipping thinking about natural keys and using surrogate keys each time, as if it is a standard.


7 Answers 7


1 - It's faster. A JOIN on an integer is much quicker than a JOIN on a string field or combination of fields. It's more efficient to compare integers than strings.

2 - It's simpler. It's much easier to map relations based on a single numeric field than on a combination of other fields of varying data types.

3 - It's data-independent. If you match on the ID you don't need to worry about the relation changing. If you match on a name, what do you do if their name changes (i.e. marriage)? If you match on an address, what if someone moves?

4 - It's more efficient If you cluster on an (auto incrementing) int field, you reduce fragmentation and reduce overall size of the data set. This also simplifies indexes needed to cover your relations.


To the specific points you just added:

1 and 2 - It's still much quicker to compare an int than a string, space considerations aside. You are also conveniently ignoring overhead needed to store length of variable length fields (normally 2 bytes per field per row).

3 - If you cluster on the ID field then it doesn't add anything extra. It SAVES space since you are using a more efficient row id.

4 - And then when that person changes their username your links all break.

5 - You really don't know what you are talking about here. You do have to store the data, that's correct, but it's much more efficient to index and JOIN on the int than on a combination of other fields.

  • 2
    "It's data-independent" As a front-end developer, this is the most important thing to me. If the data is ever being used in a front-end application--especially one under active development--this can save a lot of dev time. An id will always be URL-safe, case-insensitive, and not require additional logic. This can be solved at the service layer with a good API, but practically speaking it often turns into front-end devs writing a lot of code that shouldn't exist on the front end. In the context of a project, anything more complicated than an id field can introduce a surprising amount of effort.
    – ramblinjan
    Jul 16, 2015 at 20:37
  • "It's data-independent" -- and how do you know it is still the same person if everything changes?
    – Alexey
    May 7, 2018 at 10:06
  • @Alexey you know it's the same person because there is an update transaction that changes the identifying information. All related records are linked by the unchanged ID, but the attributes can ALL change and everything still works.
    – JNK
    May 18, 2018 at 13:08

Because people have learned from experience that using such fields leads to problems.

I've developed database applications for 20 years. Most critically I spent five years working with data warehouses. In the early days choosing another field seemed ok. Then we found duplicate records, sometimes unique validations were missing, sometimes (frequently) users had supplied different information that now needed to be merged, or whatever, and merging and managing records was a nightmare.

Even (or even particularly!) when the identifier 'seems' unique, this can turn out not to be true. For example: US Social Security Number. It should be unique to a person, right? Sure, but what if some records have been entered with SSN's that were mistyped by users in the past? Now there can be conflict issues with new, valid numbers that are entered for new records. A side note is that primary keys should also never be displayed as they lead to user assumptions about them and they are also not good for the best security model for web sites URL's.
Always consider - will the user bookmark this URL and expect it to work in the future?

So folks have learned:

Don't use a "surrogate key" (e.g. SSN) as a primary key when the surrogate has 'any' business value or meaning.
Instead, use a primary key that is unique and not derived from application data.

  • "Because people have learned from experience that not doing so leads to problems" -- the converse is also true.
    – onedaywhen
    Nov 24, 2011 at 13:24

If you want to lookup your data, you really want to do this based on an integer field or fields. This is why many people use an ID field for this.

But if you have a table you use for a many-to-many relation, it isn't really needed. Lets say you have the following two tables:

Table news id integer title varchar item text

Table tags id integer name varchar

For each item in news, you want to add one or more tags, so you create the table:

Table news_tags news_id integer tags_id integer

In this case, it really isn't needed to create an extra id column, because you won't need it at all.


Most people default to using an auto-incrementing INT for their primary key since it's the simplest way to identify the row, particularly when you have relationships between tables that need to be defined.

If you are lucky enough to be modeling something that already has a unique identifier, then I'd look at using that for the primary key (an example would be a VIN for a car, or IMEI for a cell phone).

There are also what are called compound keys, basically two or more fields in your database uniquely identify the row. Most developers I've worked with (including myself) typically don't use this. Again, the main reason for not is that it makes it more difficult to manage relationships between tables.

In the natural world, things are not defined by a unique identifier, but by their relationship with other entities. The id field really is just an artifact of relational databases. This is the basis of the whole object-relation mapping (ORM) problem.

I realize that this is a course and you must understand the content, however don't forget that there are other ways to model data outside of a relational database. The NoSQL movement is a testament to this.

  • 1
    Good info here but your two examples are cases that should be unique identifiers but in the wild are not. Both VINs and IMEI numbers are cloned for criminal purposes and guarenteed to trip up a system that expects them to be unique. Nov 23, 2011 at 23:07
  • If criminals are known to clone some identifier then surely that might be a very good reason to enforce the business rule that the attribute must be unique - i.e. not to "trip up" the system but to prevent the said duplicate values from entering the database and thereby red-flag any such attempts as potential fraud or errors requiring further investigation.
    – nvogel
    Jan 5, 2015 at 12:49
  • What if user makes a typo when entering VIN or IMEI and that typo will need to be corrected? Natural keys should never be used, period. Apr 26, 2021 at 18:56

If you can use other fields as your primary keys, then that's good. However, since you tagged this under [sql-server] I'll be able to add some info...

  • If you ever need to replicate a table that never had nor needed a primary key, then you'll have to make one. if you had this id column in place.. = easy as pie

  • ID columns, especially those that are IDENTITYcolumns are also good as indexes (sometimes) in a sense that they almost never get updated, and if you don't delete rows from the table, you decrease index fragmentation.

  • ID columns don't always have to just be identity columns. You can store a date_id (for some tables that it makes sense to do so) and if it's unique (like I said.. for example you have a table where one row = one day) then you can apply it as a key or index

  • When you don't have a create_date/entry_date column and you ever need to check data in the order they were entered.. having an ID column as an identity makes that possible.

  • An ID can act as a foreign key as well.


While compound keys work, a single primary key can be sometimes be easier to work with. For instance, on doing deletes it is very easy to single out a particular row.

It is also often more efficient to search on a numeric key.


Because ID is used to identify everything. Take an user as an example - searching user by their username is slower than by Integer (ID)

  • nobody searches each other with their id's in real life.
    – Uğur Gümüşhan
    Nov 22, 2011 at 20:21
  • @UurGümühan: I meant in backend - searching for 1 is faster than searching for Admin
    – Martin
    Nov 22, 2011 at 21:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.