We are in an era where we are living in an ocean of data essentially. Few jaw-dropping facts would clear this. In the year 2018 alone, globally, every day more than 2.5 quintillion bytes of data were created. By the end of 2020, the global data volume was estimated at 44 zettabytes.
And there is no stopping, internet users and data both are increasing at callouses but at a steady rate. Expectedly, by the end of 2025, daily data generation might reach 463 exabytes globally. Let’s check how we can use primary keys when working in Database Management Systems such as Azure, MySQL, MS Server and others that use SQL.
What Is Primary Key In SQL?
The primary key in SQL is a constraint. A primary key is one or a combination of more than one column in a database table. In a database table, each record can be uniquely identified by the Primary key. In a database table, there can be a maximum of one primary key.
The data of the primary key field value or combined value in the case of the multi-field primary key, also called a composite key, cannot be duplicated. Also, the primary key field value cannot be NULL. The primary key and the foreign key are used together to ensure referential integrity between tables in RDBMS.
Rules Of The Primary Key In SQL
The concept of the primary key in SQL stands upon the below rules:
- All the existing values in the column or columns marked as a primary key must have unique values.
- While inserting new rows in the table, duplicated primary key values cannot be inserted.
- Maximum one primary key can be created for a database table. However, a primary key may consist of multiple fields.
- NULL is not allowed as a field value in a primary key field.
The Primary Key In SQL Example
For a better understanding of the primary key in SQL, let us look at an example.
As we can see, there are no NULL values in the table above, and also column Employee_ID and column Employee_Name do not have any duplicate entries. Hence we can impose a primary key constraint on one of these 2 fields. However, we need to keep in mind, while inserting new rows, duplicate or NULL values cannot be inserted for the field specified as the primary key.
Again, column Employee_Department has duplicate values, so alone this field cannot be the primary key, but together Employee_ID and Employee_Department form a unique entry, hence a primary key can be formed using these two fields together.
The Primary Key Syntax In SQL
To check various operation syntaxes on the primary key in SQL, let’s take the example of the same table as shown above.
Primary Key on Create Table: For this 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 this table, if we want to apply the primary key constraint when the table already exists, sample syntax will be as below:
ALTER TABLE Employee ADD CONSTRAINT pk_Employee PRIMARY KEY (Employee_ID)
Drop the Primary Key for a Table: For the same Employee table, if we want to drop or delete the primary key constraint, sample syntax will be as below:
#SQL Server/ MS Access/ Oracle ALTER TABLE Employee DROP CONSTRAINT PK_Employee; #MySQL ALTER TABLE Employee DROP PRIMARY KEY;
Frequently Asked Questions
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)
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.
The bottom line is, many fields in a table can be a primary key, given that it satisfies the condition of a primary key. The primary key must be created in the table while keeping the purpose of the table in mind.
Primary keys are especially useful as managing the data appropriately and identifying each data-set uniquely is understandably important. Using the primary key in DBMS or Database Management Systems is the simplest method of identifying and pulling out unique records when using SQL.