What is a Primary Key?

A 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 which are unique. In other words, every value is unique for Primary Key.

Rules for Primary Key

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

Below diagram summarizes all the above point for the primary key.

How Primary Key Works

In this tutorial, you will learn

How to Create Primary Key

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.

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

Result: Course_Id is now a Primary Key.

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

Below is the syntax to create the table 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 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.'

Result: Course_Id is now a 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.

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.

T-SQL: Add a Primary key to existing table using Alter Table

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);

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.

 

Guru99 is Sponsored by AQUAFOLD
Aquafold

Aqua Data Studio is proud to sponsor Guru99's informative website for database professionals. Take your productivity to new heights with query tools, visual analytics, and ER diagramming for multiple platforms.