CODEWITHSUNDEEP
sqldatabase
shd
shd

Reputation: 1129

what to choose for primary key in table?

i have a huge table which contains millions of records , assuming my natural key is too large and might change in the future , i want to add a surrogate primary key , and will use that surrogate as foreign key in different tables.

most of the access for this table is searching by the natural key

Upvotes: 0

Views: 154

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65342

The best way to do this, is to use a database-generated unique key (such as AUTO_INCREMENT in MySQL and its siblings in other RDBMS) and create a combined primary key consisting of this and your natural key.

Let me explain why:

A natural key as a secondary unique key has the big disadvantage of creating very bad key localisation: Sequential key values tend to be distributed all around the disk, making very inefficient use of your key cache (no matter how it is implemented, the concept stands). Auto-Increment keys tend to be nearly perfectly localised, i.e. a single page on the disk, which translates into a single disk seek, will very likely contain a bigger part of the keyspace.

By combining a "database-natural" key with an "application-natural" key (in this sequence!!) to a combined primary key, you get best of both worlds. This is especially true, if the natural key consists of some random factor, such as a GUID.

Upvotes: 0

M. Ahmad Zafar
M. Ahmad Zafar

Reputation: 4939

My suggestion is to create a simple INT datatype primary key and make it AUTO_INCREMENT if you are using MySQL, IDENTITY if you are using MS SQL Server or SEQUENCE if you are using Oracle. The INT datatype for primary key is very good if you need performance.

The natural key should be an Indexed column for better search.

Upvotes: 2

Related Questions