SQL PRIMARY KEY: Create & Add to an Existing Table

What is Primary Key in SQL?

A primary key in SQL Server is a field, or a combination of fields, that identifies a record uniquely. It is a column or a set of columns in which every value is unique. In other words, no two rows can share the same primary key value.

Rules for Primary Key

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

The diagram below summarizes all the points above about the SQL primary key.

How to Create Primary Key in SQL Server

You can create a primary key in two ways:

• SQL Server Management Studio
• T-SQL: create the primary key while creating a new table

Let us study each method in detail.

SQL Server Management Studio

Step 1) Right-click on the table name and click Design.

Step 2) Right-click on the column name and click Set Primary Key.

Result: Course_Id is now a primary key, shown by a key icon beside the column.

T-SQL: Create a Primary Key While Creating a New Table

Below is the syntax to create a table with a primary key from T-SQL.

Syntax:

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

Here, CONSTRAINT names the key, PRIMARY KEY marks the column that must stay unique, and every column also needs a data type.

Let us 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 Execute.

Result: Course_Id is now a primary key on the COURSE_TSQL_PK table.

Example: Let us see whether it allows entering multiple records with the same Course ID.

Step 1) Insert four rows with different Course_ID values.

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 that all data was inserted successfully by running the SELECT query.

Note: You can insert duplicate values in a non-primary-key column.

Step 3) Now let us try inserting a new record with an existing Course_ID, which is the primary key.

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

Result: The system does not allow inserting the new value, because 4 already exists in the Course_ID column, which is a primary key.

How to Add Primary Key to Existing Table in SQL?

Now we will see how to add a primary key to an existing table in SQL.

You can use the ALTER statement to create a primary key. However, a primary key can be created only on columns that are defined as NOT NULL. You cannot create a primary key on a column that allows NULLs; if a column currently allows NULLs, you must first alter it to NOT NULL, or 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!

• A primary key can be a combination of multiple columns. This combination is known as the composite primary key.
• A primary key can have a maximum of 16 columns.

FAQs