by Raj Kumar

Created on: August 10, 2009

Selecting a primary key within a database table is not very tough task. But we have to take extreme care during primary key selection. This step is also a part of database normalization process which deals with removal of redundant and duplicate data. During the course of normalization, creation of primary key is an important course of action which builds relationship among database tables and the best footstep is moved forward towards compact and organized database architecture.

Within a database table, there are number of columns or fields of different data types. Among those columns, there is lot of possibility that more than one column or group of columns meet the mandatory requirements to be eligible for primary key. Normally these multiple and optional columns which seems to be probable primary key are called as candidate keys. On contrary, sometime it happens that no single column qualifies for primary key column contest. In that scenario, combination of group of columns which have unique combination of values on each row and can uniquely identify each row with that group value is considered for primary key. In that case, the group is called to form a composite key which acts as primary key for that table.

Following the definition of primary key, the candidate key should contain unique value which can provide unique row id to each row within the database table. Column or group of columns participating in the contest of primary key should not contain null values. Basic reason behind this is null values represent absence of value. Hence null value can not provide unique identification for table rows. Along with these mandatory points, a good primary key column also takes care that its value doesn't change often. If primary key value is changed, it impacts related tables in which it is referenced through foreign key relationship. Referential integrity forces the primary key changes to be reflected in all tables which have relationship with that amended value.

Often column containing unique, non null numeric value is preferred for the creation of primary key. If no column is suitable, Identity column can acts as primary key. Identity column provides auto generated unique numeric values. Primary key constraint can be defined or created using SQL statements like "CREATE TABLE" or "ALTER TABLE". They are data definition language (DDL) commands, which normally impact the schema level database changes.

As only one primary key is allowed within a database table, type of index created along with it is important. As usual the index created with the primary key column will improve the performance of search conditions and SQL Joins, whenever the indexed column is referred in SQL Joins, "WHERE" and "HAVING" clause. Actually there are two types of indexes viz. clustered index and non clustered index. When clustered index is defined along with primary key, the data within the primary key column is physically sorted as per the index value. This helps in fast sequential type of data search. Compared to multiple non clustered indexes within a database table, only one clustered index is possible within a table. Smart selection and creation of primary key certainly boost the performance of entire database and end user applications.

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