In this tutorial, I am going to discuss Primary Key in SQL Server. Primary Key is used to identify a record uniquely.

After completing this tutorial you will be able to understand:

  • What is Primary Key in SQL Server?
  • How to Create Primary Key in SQL Server?
  • Drop a Primary Key in SQL Server.
  • Enabling and Disabling a Primary Key in SQL Server.

Introduction:

Primary is a SQL Constraint that is used to Uniquely Identifies record a field with a field or a combination of the field.

Primary Key

The key point about Primary Key:

  • We can define a Primary Key for the table either in CREATE TABLE Statement or ALTER TABLE Statement.
  • Primary Key can not NULL values.
  • We can create only one Primary Key on each table.

The syntax for creating a Primary Key:

You can create a primary key in two ways. Here is the Syntax for both:

Sytanx1:



CREATE TABLE tablename
(

  Column_name1 datatype [ NULL | NOT NULL ] [ PRIMARY KEY ],

  Column_name2 datatype [ NULL | NOT NULL ],

  ...
 );

Syntax2:



CREATE TABLE tablename
(
  column_name1 datatype [ NULL | NOT NULL ],

  column_name2 datatype [ NULL | NOT NULL ],
   ...
  CONSTRAINT constraint_name PRIMARY KEY (column_name1, column_name2, ... column_name_n
);

);

The example of Primary Key:

Here we will see how to create a Primary Key as an example. Look at the following table structure.



CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
EmailID VARCHAR(150) NOT NULL,
MobileNo VARCHAR(150) NOT NULL
);

In the above table structure, you can see we have created Primary Key on EmployeeID column because the Employee is uniquely identified with his EmployeeID.

We can also create a Primary Key on the table using Syntax2.



CREATE TABLE Employee
(
EmployeeID INT,

FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
EmailID VARCHAR(150) NOT NULL,
MobileNo VARCHAR(150) NOT NULL,
CONSTRAINT employeepk PRIMARY KEY(EmployeeID)
);

We can also create a Primary key on a combination of columns. This combination is called as Composite Key.

Example:



CREATE TABLE Employee
(
EmployeeID INT,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
EmailID VARCHAR(150) NOT NULL,
MobileNo VARCHAR(150) NOT NULL,
CONSTRAINT employeepk PRIMARY KEY(EmailID,MobileNo)
);

Create Primary Key after Table Creation:

If you want to create a Primary Key after the creation of table you can use ALTER TABLE Statement to create a Primary Key on the Table. You can create a Primary on a column or a group of columns that are already defined as NOT NULL. You can not create a Primary Key on a column that can accept NULL values. For creating a Primary Key on such columns that accept NULL Values you need to drop it and recreate it.

The syntax for Creating Primary Key after Table Creation:


ALTER TABLE tablename
ADD CONSTRAINT constraintname PRIMARY KEY (column_name1, column_name2, ... column_name_n);

Example:

You can create a Primary Key using ALTER TABLE Statement in the following ways:


ALTER TABLE Employee
ADD CONSTRAINT employeepk PRIMARY KEY (EmployeeID);

You can also create a Primary Key using ALTER TABLE Statement for the group of columns using the below example.


ALTER TABLE Employee
ADD CONSTRAINT employeepk PRIMARY KEY (EmailID,MobileNo);

Drop Primary Key:

You can drop a Primary Key using the ALTER TABLE Statement.

The syntax for Dropping a Primary Key:


ALTER TABLE tablename
DROP CONSTRAINT constraintname;

The example of Dropping a Primary Key:


ALTER TABLE Employee
DROP CONSTRAINT employeepk;

Disabling a Primary Key:

You can also Disable a Primary Key on the Table.

The syntax for disabling a Primary Key on the table.

ALTER INDEX constraintname ON tablename
DISABLE;

Example:



ALTER INDEX employeepk ON Employee
DISABLE;

Enabling a Primary Key:

You can Enable a Primary Key on the Table.

The syntax for disabling a Primary Key on the table.



ALTER INDEX constraintname ON tablename
REBUILD;

Example:



ALTER INDEX employeepk ON Employee
REBUILD;

Conclusion:

Hope this tutorial is helpful for you when you have just started learning SQL Server. I will appreciate your Comments, Suggestions and Feedback.

View More:

Thank You.

Leave a Comment