Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 757
James McMahon Avatar asked Feb 10 '09 13:02

James McMahon


People also ask

Should I use auto increment for primary key?

Auto-increment should be used as a unique key when no unique key already exists about the items you are modelling. So for Elements you could use the Atomic Number or Books the ISBN number.

Is primary key automatically created?

By default, a primary key is automatically created by taking the object name, adding an ID to the object name, and assigning a primary column value of 1. If you change the value, it must be sequential, unique, and greater than 0. The sequence determines the order in which the primary index is created.

What is a native key in database?

A natural key (also known as business key or domain key) is a type of unique key in a database formed of attributes that exist and are used in the external world outside the database (i.e. in the business domain or domain of discourse).

Should I use UUID as primary key?

Using UUID for a primary key brings the following advantages: UUID values are unique across tables, databases, and even servers that allow you to merge rows from different databases or distribute databases across servers. UUID values do not expose the information about your data so they are safer to use in a URL.


2 Answers

This is a pretty common topic.

  • How do you like your primary keys?
  • What’s the best practice for Primary Keys in tables?
  • Which format of primary key would you use in this situation.
  • Surrogate Vs. Natural/Business Keys
  • Should I have a dedicated primary key field?
like image 38
cletus Avatar answered Nov 09 '22 11:11

cletus


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.

like image 88
Tomalak Avatar answered Nov 09 '22 13:11

Tomalak