This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Hello Guys,
The discussion on Primary Key is very common and even not require so much attention because each and every database professionals have excellent knowledge on 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 familiar concept, but then also sometimes 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 the leading role as a pointer.
The best data type for Primary Key.
I suggest choosing a numeric data type because storage of numeric type is very compact than any character data type and even most of the 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 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. The Application or Human has to enter SSN (social security number) into a database, and there might be chance for the wrong insertion, and maybe your system cannot detect this error at the same time, but once you find this error then you will face more problems 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 modelled 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.
Are you creating a Primary Key for each and every table?
If you are doing this, then you are doing right. But, If you are not doing this then your database may have many HEAP tables which are not good because primary key also works as cluster index and also responsible for the correct order of the data.
Your best practice is to create an auto_increment column for each and every table.
Now, a task is for you, please check your database primary key and judge yourself.
In above theory whatever I have written is my viewpoint and concern, but you might have a different view so take decision base on your system requirement.
For further suggestions, your comments are most welcome.
