CODEWITHSUNDEEP
databasedatabase-designprimary-key
James McMahon
James McMahon

Reputation: 49639

Native primary key or auto generated one?

As a rule is it better to use native primary keys (ie existing columns or combination of columns) or set your primary key to an auto generating row of integers?

EDIT:
It has been pointed out to me that this very similar to this question.

The consensus here is to use surrogate keys, which was my natural inclination, but my boss told me I should also use natural keys where possible. His advice may be best for this particular application, as Name in row uniquely identifies it and we have a need to maintain the ability to view old data, thus any changes to the name/rule is going to mean new unique row.

While the answers here are all helpful, most of them are based on the subjective "here is what you should", and do not cite supporting sources. Am I missing some essential reading or are the best practices database design highly subjective and/or application dependent?

Upvotes: 5

Views: 4772

Answers (6)

Tomalak
Tomalak

Reputation: 338316

A primary key

  1. must identify a row uniquely.
  2. must not contain data, or it will change when your data changes (which is bad)
  3. should be fast in comparing operations (WHERE clauses / joins)

Ideally, you use an artificial (surrogate) key for your rows, a numeric integer data type (INT) is best, because space-efficient and fast.

A primary key should be made of the minimum number of fields to still fulfill conditions 1.-3. For vast majority of tables this minimum is: 1 field.

For relation tables (or very special edge cases), it may be higher. Referencing a table with a composite primary key is cumbersome, so a composite key is not recommended for a table that must be referenced on it's own.

In relation tables (m:n relations) you make a composite key out of the primary keys of the related tables, hence your composite key automatically fulfills all three conditions from above.

You could make primary keys out of data if you are absolutely sure, that it will be unique and will never change. Since this is hard to guarantee, I'd recommend against it.

Upvotes: 8

cletus
cletus

Reputation: 625237

This is a pretty common topic.

Upvotes: 4

Valentin V
Valentin V

Reputation: 25759

It is an old war between purists and pragmatists. Purists don't accept surrogate primary keys and insist on using only natural ones. If you ask me, I'll vote for increment (surrogate keys) in most of situations.

Upvotes: 1

Assaf Lavie
Assaf Lavie

Reputation: 76063

Always ints.

You'll appreciate you did so when it's time to cross reference those elements in other tables (using foreign keys)

Upvotes: 2

Gary Green
Gary Green

Reputation: 22395

I would say auto generating, theres no real reason not to in my mind. Unless your developing some kind of hash table, but even so, I would stick to a unique primary key automatically created by the database. Its quick, simple and reliable. Don't reinvent the wheel if its already there.

Upvotes: 0

Otávio Décio
Otávio Décio

Reputation: 74290

Whatever it is, make it non meaningful (surrogate key). Meaningful primary keys are deadly.

Upvotes: 1

Related Questions