Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 it's size per row on 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.

share|improve this question

migrated from stackoverflow.com Nov 23 '11 at 22:04

This question came from our site for professional and enthusiast programmers.

marked as duplicate by jcolebrand Nov 23 '11 at 22:40

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3  
The real underlying logic is this - the id is a representation of that row or item as an entity. All other relational keys will just be based on it's attributes which can change and are more complicated to compare. –  JNK Nov 22 '11 at 20:25
    
I'm curious to know what your perception will be after you receive answers from stackoverflow users. Would you mind updating your post when you've come to your conclusion? –  Nonym Nov 22 '11 at 20:27
    
@Nonym - I am continuously updating the question by adding advantages of avoiding adding a new id column and using an existing unique field. –  Uğur Gümüşhan Nov 22 '11 at 20:35
    
@UğurGümüşhan What exactly are you basing these arguments on? –  JNK Nov 22 '11 at 20:38
    

7 Answers 7

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

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

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.

share|improve this answer

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.

share|improve this answer

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.

EDIT

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
1  
Great additional info. Thanks haficuk –  Michael Durrant Nov 23 '11 at 20:53
    
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. –  Mark Storey-Smith Nov 23 '11 at 23:07
    
@MarkStorey-Smith Thanks Mark! Any chance you'd be able to cite some references for me on this? –  hafichuk Nov 24 '11 at 1:31

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.

share|improve this answer
1  
For instance, 20 million SSNs are associated with more than one person. Fields that seem unique are many times not. dailyfinance.com/2010/08/12/… –  Cody C Nov 23 '11 at 15:34
    
"Because people have learned from experience that not doing so leads to problems" -- the converse is also true. –  onedaywhen Nov 24 '11 at 13:24
    
@Michael Durrant Your last comment is really inspiring. I often hear people mention that if a key is stable and unique then make it a primary key. However, I always felt that any key that is entered by a user has a probability for change even if it comes from a standard (ISO2 for example). Do you then recommend always using a surrogate key instead of a natural key? –  Songo Jan 7 '13 at 14:55
    
Yes. Updated the answer. –  Michael Durrant Jan 7 '13 at 17:42
    
FYI Database 101 teaches that if user data should never be used as a key field. You need to allow for duplicate SSN and still create a unique key ID for each record. –  htm11h Nov 10 at 13:54

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