by Raj Kumar

Created on: August 05, 2009

The process of selecting and creating a primary key on a column or group of columns within a database table is not a simple step of just creating an index to speed up the search process. If we go by the definition of primary key constraint, the column or group of columns, participating in the creation of primary key, should meet certain mandatory conditions. But a normal column which is involved in SQL Joins, WHERE and HAVING clause, can have an appropriate index. This can improve the performance of desired SQL query based on the index created on that column. Primary key needs equally or more attention than this. If not properly chosen, it can impact the structure and schema of entire database which in turn make the end user application bad to worse.

To maintain healthy relationship among database tables and create good architecture, primary key play an essential role. There can be multiple columns or group of columns which seem to look like suitable candidate to become primary key. Hence they are called as candidate keys. But lot of care should be taken and decision should be made wisely while converting any of these candidate keys into primary key. Every possible scenario should be checked and assessed before approaching to any final conclusion. Best candidate key should be judged and promoted to the post of primary key.

Normally primary key constraint can be applied to a single column but if two or more columns involve in primary key creation, it is called as composite key. Their combination should contain unique value which can uniquely identify rows within the database table. They can not contain NULL value. NULL value is nothing but no value or absence of value which can not help in uniquely identifying the row within a table. Mostly numeric column is preferred over text column while deciding the primary key. These features are strictly followed while configuring the primary key. At many places, Identity columns are recommended as primary key. Identity column contains auto generated unique values with a specific predetermined seed value (starting value) incremented by fixed interval number.

Change of value in the primary key column is also an important thing which should not be ignored. There are two types of database indexes viz. Clustered and Non-clustered. Normally clustered index is created on primary key column. But non-clustered key can also be created instead of clustered index. Clustered index physically sort and store the table values in primary key column. Only one primary key is allowed per database table. In relational database, primary key is referred in other table and matched with foreign key to build one-to-one, one-to-many or many-to-many relationship between multiple tables. If the value in primary key column for any row changes frequently, corresponding changed value should be reflected at so many places to maintain data integrity and synchronization. Hence that column can not be considered as good candidate for primary key, if its value changes most often. By keeping all these important parameters in mind, we can smartly choose an ideal primary key.

Learn more about this author, Raj Kumar.
Click here to send this author comments or questions.