Primary Key Constraint in SQL

If you are a beginner or intermediate then this article gives you a good overview of the primary key constraint and if you are an expert then it may help you to get something new on the same or help to refresh your knowledge. Well, in this article post, you will learn all about primary keys with FAQs to maintain the integrity and accuracy of the data.

Introduction to Primary Key

A primary key constraint is a fundamental aspect of a database design and a very popular part of key constraints in SQL that acts as a unique identifier for each record in a database table. The concept of a primary key is a very crucial component of SQL that ensures the uniqueness of data in database tables. It helps to store, retrieve and organize data in a fast and efficient way. A primary key is used to ensure the data integrity and accuracy of the data in a table.

What is a Primary Key Constraint?

The primary key constraint is a unique identifier which is the value of a column or set of columns that is unique for all rows in the table. Primary Key does not allow Nulls or it is a combination of a NOT NULL and uniquely identifies each row in a table. The primary key helps to make relationships between various tables within the database. The primary key does not allow duplicate values and creates a clustered index automatically. It is also implemented while adding a unique index to the specific column or set of columns in the table.

It also helps to maintain the entity integrity of the data stored in the tables. The database will not work or function properly at all if the primary key is not defined on tables. As per the standard of RDBMS, Every table must have a primary key defined which can be applied to single or multiple columns or fields.

How Does a Primary Key Work?

A primary key is implemented with a combination of a unique constraint and a unique index. The unique constraint ensures that the value in the primary key column can not be duplicated or null. The unique index ensures that the values in the primary key column are unique in the table.

Properties of Primary Key

Refer below the properties of primary key –

1. A primary key can be applied in multiple columns of a table but a table can have only one primary key in it.
2. If a primary key is applied on only one column then it is called a primary key but when it is applied on multiple columns then it is known as a composite key.
3. A column of a table must have assigned a not-null where a primary key is applied.
4. A primary key does not allow duplicate records or values where it is applied.
5. A primary key can be applied to a table using create and alter table statements.
6. A primary key length is fixed which can not be exceeded by 900 bytes and also it enforces entity integrity.
7. A primary key creates a clustered index automatically in the column where it is applied.

Levels of Primary Key

A primary key can be applied or created into tables at the below levels –

1. Column Level
2. Table Level

Why Use Primary Key?

There are various benefits and reasons to use primary key in databases as follows –

1. Ensuring Entity Integrity: Primary key ensures that the data stored in a table is unique, accurate, and up-to-date where it is defined. This constraint maintains the entity integrity and acts as a unique identifier for the data stored in the tables. It helps to eliminate duplicate data possibilities always.
2. Enforcing Referential Integrity: A Primary key also enforces the referential integrity using a foreign key in one table that must match a primary key in another. This referential integrity maintains the relationship between tables and ensures that the data in the table is consistent and accurate.
3. Enabling Data Relationships: Primary keys are being used to make relationships between tables of databases for the aspect of data management and analysis purposes.
4. Improving Query Performance: By using primary key, it creates a clustered index that helps quickly execute the query, retrieve the data, and improve query performance.
5. Prevention of Duplicate Data: Primary key helps to prevent duplicate data while being inserted or updated into the database that can lead the incorrect or inconsistent data.

How to Use Primary Key Effectively?

Refer to the below guidelines in order to use primary key effectively –

1. It is very important to identify a column that should have a unique value that may not change in the future when choosing a column to use as the primary key.
2. In a few cases, it may be necessary to use the primary key on multiple columns to ensure that each row has a unique identifier.
3. In a few cases, it may be necessary to use a surrogate key which is also a unique identifier generated by DBMS. The surrogate key will be useful when the primary key does not fulfill the requirement of a unique identifier.

Application of Primary Key in SQL

You will learn here all about the application of primary key in detail as follows –

1. Create a Primary Key in SQL

Creating and applying a primary key in SQL is a very simple process. You will learn here the creation and application of primary key using MS SQL queries as follows –

I) Primary Key Creation using SQL Queries Column Level at the time of table creation

You will learn here to create a primary key at the column level while creating a new table.

a. Primary Key Creation with one Column at the column level without providing a name of PK

In this example, we are creating a table (PK_Test1) where a primary key is associated with the ‘Vendor_Id’ column, and the primary key is added to ensure the uniqueness of the Vendor_Id columns data. Also, you can see that we have not provided any specific name for this primary key, and in this scenario, SQL Server DBMS will provide a system-generated default name for this primary key as follows –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE PRIMARY KEY,
ColumnN DATATYPE);

Example:

CREATE TABLE PK_Test1(
Vendor_Id INT PRIMARY KEY,
Vendor_Name VARCHAR(30) NOT NULL);

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for this primary key. In this scenario, SQL Server DBMS has provided a system-generated default name for this primary key (PK__PK_Test1__D9CCC2A835CF2F80), which can be seen in the above screenshot marked with yellow color on the left side.

b. Primary Key Creation with one column at the Column Level with providing a name of PK

In this example, we are creating a table (PK_Test2) where a primary key is associated with the ‘Vendor_Id’ column, and we have provided a specific name ‘pk_test2_vendor_id’ for this primary key as follows –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE CONSTRAINT Constraint_Name PRIMARY KEY,
ColumnN DATATYPE);

Example:

CREATE TABLE PK_Test2(
Vendor_Id INT CONSTRAINT pk_test2_vendor_id PRIMARY KEY,
Vendor_Name VARCHAR(30) NOT NULL);

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided a specific name (pk_test2_vendor_id) for this primary key, which can be seen in the above screenshot marked in yellow color on the left side.

II) Primary Key Creation using SQL Queries Table Level at the time of table creation

You will learn here to create a primary key at the table level while creating a new table.

a. Primary Key Creation with one Column at the table level

In this example, we are creating a table (PK_Test3) where a primary key (pk_test3_vendor_id) is associated with the ‘Vendor_Id’ column as follows –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE,
COLUMN2 DATATYPE,
ColumnN DATATYPE,
CONSTRAINT Constraint_Name PRIMARY KEY(Column_Name));

Example:

CREATE TABLE PK_Test3(
Vendor_Id INT,
Vendor_PAN INT,
Vendor_Name VARCHAR(30) NOT NULL,
CONSTRAINT pk_test3_vendor_id PRIMARY KEY(Vendor_Id));

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided a specific name (pk_test3_vendor_id) for this primary key, which can be seen in the above screenshot marked in yellow color on the left side.

b. Primary Key Creation with more than one Column at the table level

In this example, we are creating a table (PK_Test4) where a primary key (pk_test4_vendor_id_Pan) is associated with the ‘Vendor_Id’ and ‘Vendor_PAN’ columns as follows –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE,
COLUMN2 DATATYPE,
ColumnN DATATYPE,
CONSTRAINT Constraint_Name PRIMARY KEY(Column_Name1,Column_Name2,Column_NameN));

Example:

CREATE TABLE PK_Test4(
Vendor_Id INT,
Vendor_PAN INT,
Vendor_Name VARCHAR(30) NOT NULL,
CONSTRAINT pk_test4_vendor_id_Pan PRIMARY KEY(Vendor_Id,Vendor_PAN));

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided a specific name (pk_test4_vendor_id_Pan) for this primary key, which can be seen in the above screenshot marked in yellow color on the left side.

Note: In the above example there is primary key is made up of two columns Vendor_Id and Vendor_PAN.

III) Primary Key Creation using SQL Queries Table Level after table creation

You will learn here to create a primary key at the table level on an existing table.

a. Primary Key Creation with One Column at the Table Label (Without Providing a Name)

In this example, we have already created a table (PK_Test5) where missed adding a primary key on the ‘Vendor_Id’ column which is a not-null field. Now, we will alter that table and add a primary key on the ‘Vendor_Id’ column and also we will not provide the name of the primary key. In this scenario, SQL Server DBMS will provide a system-generated default name for this primary key as follows –

Syntax:

ALTER TABLE Table_Name
ADD PRIMARY KEY (Column_Name);

Example:

ALTER TABLE PK_Test5
ADD PRIMARY KEY (Vendor_Id);

Screenshot: Below screenshot for reference –

As you can see that the primary key is created now and we have not provided any specific name for this. In this scenario, SQL Server DBMS has provided a system-generated default name (PK__PK_Test5__D9CCC2A81CD4A038) for this primary key, which can be seen in the above screenshot marked with yellow color on the left side.

b. Primary Key Creation with One Column at the Table Level (With Providing a Name)

In this example, we have already created a table (PK_Test6) where missed adding a primary key on the ‘Vendor_Id’ column. Now, we will alter that table and add a primary key on the ‘Vendor_Id’ column and also provide a specific name ‘pk_test6_vendor_id’ of the primary key as follows –

Syntax:

ALTER TABLE Table_Name
ADD CONSTRAINT Primary_Key_Name PRIMARY KEY (Column_Name);

Example:

ALTER TABLE PK_Test6
ADD CONSTRAINT pk_test6_vendor_id PRIMARY KEY (Vendor_Id);

Screenshot: Below screenshot for reference –

As you can see that the primary key (pk_test6_vendor_id) is created now and that can be seen in the above screenshot marked in yellow color on the left side.

c. Primary Key Creation with More than One Column at the Table Level (With Providing a Name)

In this example, we have already created a table (PK_Test7) where missed adding a primary key on the ‘Vendor_Id’ and ‘Vendor_PAN’ column. Now, we will alter that table and add a primary key on the ‘Vendor_Id’ and ‘Vendor_PAN’ columns and also provide a specific name ‘pk_test7_vendor_id_Pan’ of the primary key as follows –

Syntax:

ALTER TABLE Table_Name
ADD CONSTRAINT Primary_Key_Name PRIMARY KEY (Column_Name1, Column_Name2, Column_NameN);

Example:

ALTER TABLE PK_Test7
ADD CONSTRAINT pk_test7_vendor_id_Pan PRIMARY KEY (Vendor_Id,Vendor_PAN);

Screenshot: Below screenshot for reference –

As you can see that the primary key (pk_test7_vendor_id_Pan) is created now and that can be seen in the above screenshot marked in yellow color on the left side.

Note: If you use the ALTER TABLE statement to add a primary key, the primary key columns must have not-null values, otherwise primary key will not be created or added and also throw an error.

2. Delete or DROP a Primary Key in SQL

You will learn here to delete or drop the existing primary key using GUI and SQL queries both. The following is an example to modify the primary key in an SQL server –

I) Primary Key Deletion using GUI

You will learn here, how to delete or drop a primary key using Microsoft SQL Server’s graphical user interface (GUI) as follows –

1. Go to ‘Object Explorer’ of Microsoft SQL server management studio on the left side of the window and expand your desired database
2. Now look at the Tables and expand it
3. Under the tables look for the Keys and expand it
4. Under the Keys you will be able to see the desired primary key
5. Now right-click on your desired primary key and click on Delete
6. Once clicked on Delete then a Delete Object window will appear, click on the OK button to proceed
7. After clicking on the OK button the desired primary key will be deleted permanently.

Screenshot: Below screenshot for reference –

II) Primary Key Deletion using SQL Queries

You will learn here, how to delete or drop a primary key using SQL queries as follows –

Syntax:

ALTER TABLE Table_Name
DROP CONSTRAINT Primary_Key_Constraint_Name;

Example:

ALTER TABLE PK_Test7
DROP CONSTRAINT pk_test7_vendor_id_Pan;

Screenshot: Below screenshot for reference –

As you can see that the primary key (pk_test7_vendor_id_Pan) is deleted permanently now and that can not be seen in the above screenshot marked in yellow color on the left side.

3. To Check the Behavior of a Primary Key in SQL

You will learn here, how to check the behavior of an existing primary key using SQL queries. If you remember that we have already created a table named ‘PK_Test4’ where the columns are – Vendor_Id, Vendor_PAN, and Vendor_Name. Now, We will try to insert the two times the same value to check whether it is working or not. So Let’s start as follows –

Example 1: In the following example, we are executing an insert query in ‘PK_Test4’ table –

INSERT INTO PK_Test4 (Vendor_Id, Vendor_PAN, Vendor_Name)
VALUES (12,1256,'New York, USA');

Screenshot: Below screenshot for reference –

As you can see that the above query is executed and the provided data is stored in the table.

Example 2: Now we will try to execute the same query with duplicate data to insert but the primary key should have to throw an error and abort to store the duplicate data. So let’s try as follows –

INSERT INTO PK_Test4 (Vendor_Id, Vendor_PAN, Vendor_Name)
VALUES (12,1256,'New York, USA');

Screenshot: Below screenshot for reference –

As you can see that the above query is executed but terminated by the primary key (pk_test4_vendor_id_Pan) associated with the combinations of columns ‘Vendor_Id’ and ‘Vendor_PAN’ into the table ‘PK_Test4’. Refer to the error message – ‘Violation of PRIMARY KEY constraint ‘pk_test4_vendor_id_Pan’. Cannot insert duplicate key in object ‘dbo.PK_Test4′. The duplicate key value is (12, 1256).’ So, this way we can check the behavior of the primary key in SQL.

FAQs

Conclusion

This tutorial post described the basic to advanced level of knowledge of primary key constraints. Primary key are a fundamental aspect of the database management system. PK ensures data integrity, referential integrity, improves query performance, and enables data relationships within the database. It is very important to understand the purpose and benefits of a primary key to implement it into your database. It will provide enough understanding for beginners and professionals.

We hope you would have liked this tutorial very well. Please share this on your social media network so that it can be available to even more needy persons which may be beneficial for them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

What is SQL Full Form and Its Key Feature?	What is RDBMS Full Form and Its Key Feature?
DDL Full Form and How is it used in SQL?	DML Full Form and How is it used in SQL?
DCL Full Form and How is it used in SQL?	TCL Full Form and How is it used in SQL?
What is DQL in SQL?	What is Database Language?
What is Data Integrity in SQL?	What is Integrity Constraints in DBMS?
Key Constraints in DBMS With Fundamentals	Unique Key Constraint in SQL