What Is Primary Key In SQL?

Constraints in SQL ensure a set of regulatory restrictions for the data in a table. Constraints restrict the type of data to be inserted in the table. Therefore, enables the DBMS to enforce consistency, integrity and exactitude of the data. SQL constraint primary keys can be similarly used for this purpose. Below are a few most commonly used constraints in SQL.

NOT NULL: Does not allow inserting NULL value in the specified column
UNIQUE: Does not allow inserting of duplicate values in the specified column
PRIMARY KEY: Combination of NOT NULL and UNIQUE. Does not allow to insert NULL or duplicated values in the specified column
FOREIGN KEY: Maintain referential relation between tables
DEFAULT: Insert a specified default value, if nothing is specified during data entry
CHECK: Check and ensures specified condition is fulfilling for a field value of a column
CREATE INDEX: Ensure faster access and data entry

The primary key in SQL is a non-NULL, unique constraint in a table, which helps to identify each record of a database table and prevent duplicate entries in a database table. One table can have only one primary key and a primary key may be formed using single or multiple fields.

The foreign key is also a SQL constraint based on a primary key. A foreign key in one database table refers to a primary key in another database table. Foreign key helps build referential relations between files. The primary keyed table is called the parent table and the foreign keyed table is called the child table.

A non-relational database also called a NoSQL database is used to store non-tabular data. Unlike a relational database, it doesn’t use tables to store data. Hence, a non-relational database does not have the concept of the primary key.

The primary key constraint can be applied to a table in two ways. While creating the file we can create a primary key for the table or in an existing table we can add the primary key constraint. Let’s check both ways using the below example.



Primary key on Create table: For the above table, if we want to apply the primary key constraint while creating the table, sample syntax will be as below:

#SQL Server/ MS Access/ Oracle
CREATE TABLE Employee (
Employee_ID int NOT NULL,
Employee_Name varchar(255) NOT NULL,
Employee_Department varchar(255) NOT NULL,
CONSTRAINT pk_Employee PRIMARY KEY (Employee_ID)
);
#MySQL
CREATE TABLE Employee (
Employee_ID int NOT NULL,
Employee_Name varchar(255) NOT NULL,
Employee_Department varchar(255) NOT NULL
PRIMARY KEY (Employee_ID)
);

Primary key on Alter table: For the above table, if we want to apply the primary key constraint on the existing table, sample syntax will be as below:

ALTER TABLE Employee 
ADD CONSTRAINT pk_Employee PRIMARY KEY (Employee_ID);

The main purpose of the primary key is faster data retrieval. When the primary key is used in a selected query to retrieve data, it creates a clustered index, a faster data access mechanism.

Using the primary key every record in a database table can be uniquely identified. However, this can be achieved using the unique key constraint as well. By using the primary key and the foreign key, referential relations can be established between tables.

The primary key in SQL works as a restriction, which prevents inserting NULL or duplicate values in the keyed column or columns (in the case of the composite key). In SQL, when there is a primary key in the table, data sets in the table automatically create a clustered index on the keyed column.

When table data is accessed using the clustered index, i.e. the primary key, response time becomes much faster when compared to non-clustered indexed data access. SQL alters primary keys when required as well through ALTER PRIMARY KEY statements.

When a primary key is formed by the combination of more than one column in a table, that primary key is called a composite key in SQL. Individually those column values may not be unique but together they must form a unique combination. However, there must not be any NULL values.


In the table shown above, Employee_ID and Employee_Department can form a composite key, though the Employee_Department column does not contain unique values. 

There are three differences between the unique key and the primary key.
The unique key constraint allows NULL value entry but the primary key constraint does not. In other words, the primary key constraint is a combination of the Unique constraint and not-NULL constraint.

In a table, only one primary key is allowed, however, there could be multiple unique key constraint fields in a table.

Data retrieval using the unique key creates a non-clustered index, hence retrieval is relatively slower but data retrieval using the primary key creates a clustered index hence retrieval is relatively much faster.

The phone number may or may not be a primary key depending on the nature of the table. Let us see different cases one by one.

Case 1: Let’s say, by phone number, we are considering a desk phone or landline phone. Now, while creating an employee table, it is often observed that the same desk phone is shared by multiple employees. In that case, the phone number will not be unique for each employee and it cannot be used as a primary key.

Case 2: Going by the similar example mentioned above, let’s say this time we store a mobile number as the phone number. Now, multiple employees cannot have the same mobile number hence it can be used as a primary key. However, though sounds are far-fetched in this era, if we consider, an employee may not have a mobile number, in that case, there will be a NULL entry for that employee’s phone number, and in such a scenario mobile number too cannot be used as a primary key.

Case 3: Now, take an example of an old land-line telephone directory. If we consider the telephone directory as a table, the land-line phone number is unique and non-NULL for every entry. In that case, the phone number can be the primary key for the telephone directory.