What is Primary Key in SQL?

Primary Key is a field or a combination of fields that identify a record uniquely. The Primary key is a column or set of columns that are unique. In other words, every value is unique for Primary Key.

Rules for Primary Key

  • Each table can have only one SQL Primary Key.
  • All the values are unique and Primary key SQL value can uniquely identify each row.
  • The system will not allow inserting a row with SQL Server Primary Key which already exists in the table.
  • Primary Key cannot be NULL.

Below diagram summarizes all the above point for the SQL Primary Key.

SQL Primary Key
How Primary Key SQL Works

In this tutorial, you will learn

  • How to Create Primary Key in SQL Server
  • Using SQL Server Management Studio
  • Using T-SQL: Create Table
  • How to Add Primary Key to Existing Table in SQL?
  • Interesting Facts!

How to Create Primary Key in SQL Server

We can Create a Primary Key in 2 ways:

1. SQL Server Management Studio

2. T-SQL: Create Primary while creating a New Table

SQL Server Management Studio

Step 1) Right Click on the Table name. Click on Design.

SQL Primary Key

Step 2) Right-click on the Column name. Click on 'Set Primary Key'

SQL Primary Key

Result: Course_Id is now a Primary Key.

SQL Primary Key

T-SQL: Create a Primary key while creating a New Table.

Below is the syntax to create table with Primary Key from T-SQL

Syntax: 

CREATE TABLE <Table_Name> ( Column1 datatype, Column2 datatype,CONSTRAINT <Name> PRIMARY KEY (Column name) . );

Let's create a Table with One Column as a SQL Primary Key.

Query: 

CREATE TABLE COURSE_TSQL_PK  (Course_ID Int not Null,  Course_name Varchar(20)  CONSTRAINT PK PRIMARY KEY (Course_ID)	 )

Step 1) Run the Query by clicking on 'Execute.'

SQL Primary Key

Result: Course_Id is now a Primary Key.

SQL Primary Key

Example: Let's see if it allows entering Multiple Records with Same Course ID.

Step 1) Insert 4 rows with different Course_ID 

Insert into COURSE_TSQL_PK values (1,'SQL'); Insert into COURSE_TSQL_PK values (2,'Python'); Insert into COURSE_TSQL_PK values (3,'SQL'); Insert into COURSE_TSQL_PK values (4,'C');

Step 2) Verify all Data Inserted successfully by running the Select query.

SQL Primary Key

Note: We can insert duplicate values in the Non-Primary key.

Step 3) Now let's try Inserting New records with an existing Course_ID which is Primary Key.

Insert into COURSE_TSQL_PK values (4,'JAVA');

Result: System does not allow inserting new value as 4 is there Course_ID column which is a Primary Key.

SQL Primary Key

How to Add Primary Key to Existing Table in SQL?

Now we will see how to add Primary Key to existing table in SQL:

You can use the ALTER statement to create a primary key. However, the primary key can only be created on columns that are defined as NOT NULL. You cannot create a primary key on a column that allows NULLs. If you need to do, you have to drop and recreate the table.

Here is the syntax: 

ALTER TABLE tableName ADD CONSTRAINT constraintName PRIMARY KEY (column_1, column_2, ... column_n);

For example: 

ALTER TABLE students ADD CONSTRAINT students_pk PRIMARY KEY (admission);

SQL Primary Key

We have added a primary key constraint to an already existing table. The constraint has been added on the admission column and assigned the name students_pk.

Interesting Facts!

  • The Primary key can be a combination of multiple columns. This combination is known as the Composite primary key.
  • The Primary key can have a maximum of 16 columns.

Summary

  • Each Table can have only one Primary Key
  • All the values are unique which can uniquely identify each row.