by Raj Kumar

Created on: August 03, 2009

A database management system can not be relational without Primary Key constraint. Apart from enforcing data and entity integrity within a database table, Primary Key column participates in creating and maintaining a valid relation between two or more tables. To call any database relational, there should be some association between two tables. Based on that association, row in one table find match with rows in another table. Due to the existence of that match, we can say that a row in first table is related to one or more rows in second table. This means that two tables are related to each other based on some key values which identify each row uniquely within respective tables.

Duplicate identifier values can not identify two rows uniquely within a database table. Hence the identifier value should be unique to provide unique row identification. Uniqueness should also be combined with non NULL values because NULL value indicates absence of value which in turn can not identify any row in a table. This means that the identifier should not allow NULL values. Since the identifier guarantees the data uniqueness which uniquely identify each row in the table, no other column in the table should be allowed to act as row identifier. If we combine all these features, we indirectly imitate the behavior of primary key. This implies that primary key is needed for relational database.

If a column or combination of columns that have distinct values which can uniquely identify each row in the table and are non null, that column or combination of columns can participate in implementing Primary Key constraint. When we specify the primary key, unique index or clustered index is automatically created for that column. Due to the creation of clustered index, data stored in Primary Key column is physically sorted. Hence the index permits faster data access when it is used with SQL Joins or search criteria based on the WHERE and HAVING clause in database queries.

We can relate the Primary Key value to another table by creating Foreign Key constraint. Foreign key column in second table can contain duplicate or unique values. Using Primary and Foreign Key within database tables, we can create relationship among them. These steps fall under the process of database normalization. Normally numeric column is preferred for primary key, for example, Identity column which provides unique auto generated numbers. There can be many columns which fulfill the criteria for Primary Key and hence they are called as Candidate Key. But the ideal Candidate Key which can turn out to be Primary Key depends on many factors. One of the important factors is that the Primary Key column value should not change frequently. When two or more columns take part in the creation of Primary Key constraint, the resulting combination of columns is called as composite key.

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