SQL PRIMARY KEY: How to Create & Add to Existing Table

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.

In this tutorial, you will learn

• How to Create Primary Key in SQL Server
• Using SQL Server Management Studio
• Using T-SQL: Create Table
• Using T-SQL: Alter Table (Add to Existing Table)
• 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.

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 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.'

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

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

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.