Primary key in database

Primary key is a single column in a relational database table that uniquely identifies all the records in that table. Here the important distinction is that the primary key must be unique for each row. Because of that characteristic, you can use the key to identify a special row of data or records.

The primary key must contain the following characteristics:

  • Each table in the relational database must contain only one primary key.
  • Primary key cannot contain a null value.
  • It will never change in value. So, a primary key cannot contain data that might change.
  • It must be unique. No two rows can contain the same primary key value.
  • Sometimes, more than one column or set of column can act as the primary key.
  • The PRIMARY KEY constraint in a Primary key enforces uniqueness of the column or column combination that helps to uniquely identify each row in a table.

Purpose of Primary key in database

The special purpose of primary key in the relational database is to implement relationship between tables in database.

Primary key also allows user to uniquely identify each row in a database table.

Primary key in SQL

Here, for example, Customer is a table in a database. The table contains six columns and CustNO would be created as primary key . To create this table in SQL the command is as follows.

Primary key inSQL

This create table statement has only one PRIMARY KEY constraint. Here, the CustNO column is the primary key. By declaring CustNO column as a primary key it is ensured that CustNO column will not allow to entry duplicate number or null value.

The RDBMS will generate the table with few records as follows:

Primary key
Composite primary key

Keys don’t have to be purely numeric.  Any value is valid as a Primary key, as long as it is guaranteed to be unique for each individual record in the table. If more than one column or set of column act as the primary key ,this type of primary key is called concatenated key, a compound key, or, more often, a composite key.

The database designer decides which combination of columns in a table most accurately and efficiently reflects the business situation. This does not mean the other data isn’t stored, just that column or one set of column which satisfies the preceding discussion is chosen to serve as the primary key.

Related posts :