Best practice for Primary Key in Database Management System

Hello Guys,
I know that discussion on Primary Key is very common and even not require so much attention on this because each and every database professional has very good knowledge of the Database Primary Key.

I agree that you all are using Primary Key in your database system, but still I want to write theory about: what should be your best practice with Primary Key.

This is a very common concept, but then also some time database professionals are making a mistake and even they don’t know about it.

Let me start this theory,

First, I would like to define the Primary Key.

A Primary key is a unique identifier for each record, but this is the only half story. Another main purpose is to relate records for different tables and playing main role as one pointer.

The best data type for Primary Key.

I suggest to choose a numeric data type because storage of numeric type is very compact then any character data type and even most of primary key is a foreign key for another table so smaller key performs faster than the other.

Are you updating your primary key?

Primary key should never change or update. The updating primary key might create more problem because it used by multiple indexes and used as a foreign key.

Are you using Natural Key as a Primary Key?

What is Natural Key?
A natural key is a key that is formed of attributes that already exist in the real world. For example, a US citizen’s social security number could be used as a natural key.

I suggest to avoid natural key because of one main reason which is human error. Application or Human has to enter SSN (social security number) into a database and there might be chance for wrong insertion and maybe your system can not detect this error at the same time, but once you find this error then you will be facing more problem for changing the data of the primary key.

You can use this, but you should make sure about the correct data and also using of Natural Key can prevent some additional joins.

Are you using Surrogate Key as a Primary Key?

What is Surrogate Key?
A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural key which is derived from application data.

I suggest to use Surrogate Key as Primary Key which is generated by database management system and not derived from any application data. In the background, Your primary key handled by database program and application user never aware about it.

Are you creating a Primary Key for each and every table?

If you are doing this, then you are doing right as per my view. But, If you are not doing this then your database has many HEAP tables which is not good because primary key also work as cluster index and also responsible for correct order of the data.
Your best practice is to create an auto_increment column for each and every table.

Now, task is for you, please check your database primary key and judge yourself.
In above theory whatever I have written is my view and concern, but you have might be a different view so take decision based on your system requirement.

If you have some different or additional truth about primary key, then your comments are most welcome.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

2 Comments on "Best practice for Primary Key in Database Management System"

Notify of

Sort by:   newest | oldest | most voted
6 months 16 days ago

Your posting article really usfull to sql developer/dba

4 months 3 days ago

This blog is very useful for me. I got lots of benefits like remove duplicate records in mysql.
Yes Anvesh ! I agree to have a Primary key for each table.