Why should I create an ID column when I can use others as key fields? [duplicate]

Question

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

• 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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 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)