SQL Primary Key Constraint

SQL Primary Key constraint is used to implement the data Integrity in tables. When you set a Primary key on any column, the Database engine will automatically create a Unique index on that column. In real-time, we use these SQL Primary key columns to identify the columns uniquely and to access the data quickly and effectively.

In general, every table has at least one column that contains unique values called the SQL Primary Key column. For example, an Employee table might have Employee ID, or Orders have an Order ID column. Before we get into the SQL Server Primary Key example, the following are the list of points you should remember:

A table can contain Only one Primary key in SQL Server.
You can’t insert any duplicate content into this columns.
This column doesn’t allow NULL values. By default, NOT NULL will be assigned to the these columns, even if you forgot at the time of creation.
If you forgot to mention the index as Clustered or Non clustered, then the Database engine will assign clustered index by default.

Create SQL Primary Key constraint

We can create a primary key constraint using both the Transact query and the Management Studio.

SQL Create Table Primary Key

Let us see how to create a Primary Key using a transact query. Here we will define it at the creation of the table.

CREATE TABLE [CustmerRecords]
(
  [CustomerKey] [int] NOT NULL PRIMARY KEY,
  [FirstName] [varchar](50) NOT NULL ,
  [LastName] [varchar](50) NULL,
  [BirthDate] [date] NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [Yearly Income] [money] NULL,
  [Profession] [nvarchar](100) NULL
)

We just added the PRIMARY KEY word to the Column declaration. SSMS is intelligent enough to create it for you. Refer to Create Table article in SQL Server.

See the internal code generated by the SSMS, by right-clicking on the table -> script as – > create to new query window option

SQL Create Table along with Primary Key Constraint 6

Create Primary Key on Existing table

Let us add SQL Server Primary Key on the existing table. For this, we create a new table

CREATE TABLE [CustmerRecords]
(
  [CustomerKey] [int] NOT NULL,
  [FirstName] [varchar](50) NOT NULL ,
  [LastName] [varchar](50) NULL,
  [BirthDate] [date] NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [Yearly Income] [money] NULL,
  [Profession] [nvarchar](100) NULL
)

Now let me add this constraint to the above table. For this example, we are using the following Alter Table Statement to alter table content. And then, we used the ADD Constraint statement to add this constraint.

ALTER TABLE [CustmerRecords]   
ADD CONSTRAINT PK_CustmerRecords_CustomerKey PRIMARY KEY CLUSTERED (CustomerKey);  
GO

Alter Table Add constraint Primary Clustered 7

Insert rows into Primary key Constraint Column

Let me insert a few rows to check the functionality.

INSERT INTO [dbo].[CustmerRecords] 
VALUES (1, 'Imran', 'Khan', '10-08-1985', 'abc@abc.com', 15900, 'Skilled Professional')
      ,(2, 'Doe', 'Lara', '10-08-1985', 'xyz@abc.com', 15000, 'Management')
      ,(3, 'Ramesh', 'Kumar', '10-08-1985', 'mnop@abc.com', 65000, 'Professional')

(3 row(s) affected)

Let us see the inserted data on the SQL Primary Key column.

Insert Duplicate into Primary Constraint Column

Above Insert Statement will work fine because we are inserting unique records for the customer Key. Let me insert the duplicate value into the CustomerKey column. Please refer Unique index article.

INSERT INTO [dbo].[CustmerRecords] 
VALUES (1, 'Tutorial', 'Gateway', '10-08-1985', 'myc@abc.com', 15900, 'Skilled Professional')

Insert Duplicate records in to SQL Primary Key constraint column 10

It is throwing an error: Violation of constraint ‘PK_CustmerRecords_CustomerKey’. Cannot insert duplicate key in object ‘dbo.CustmerRecords’. The duplicate value is (1).

Let me insert the Null Value into the SQL Server primary key column. For this, we added new customer record with all the values except for the CustomerKey column, i.e., NULL value..

INSERT INTO [dbo].[CustmerRecords] 
VALUES (NULL, 'Tutorial', 'Gateway', '10-08-1995', 'myc@abc.com', 15900, 'Management')

It is also throwing an error.

Insert Null values into SQL Primary Key constraint column error 11

Set Constraint using SSMS

To create a Primary Constraint using the Management Studio, please go to the Object Explorer. Right-click on that and select the Design option

SQL Primary Key using Management studio 1

Once you click the Design option, it will open the Custrecords in design mode. It has 7 columns of different data types, and our job is to add a SQL Primary Key constraint to the Customerkey column.

Right-click on the CustomerKey column and choose the Set Primary Key option.

Once you select the Set option, SSMS will set the constraint for you.

Modify SQL Primary key Constraint

Please select the table on which your constraint present, then go to the Indexes folder. Next, expand the Indexes folder to find the constraints available on it, and Right-click on it will open the context menu. Please select the option as per the requirement

Rename Option to modify the existing Primary 12

NOTE: You can’t alter the constraint using the query. To modify the existing one, you have to delete and recreate it.

Delete SQL Primary Key using DROP Constraint

If you know the constraint name, use the DROP Constraint statement along with the ALTER TABLE Statement

ALTER TABLE [CustmerRecords]  
DROP CONSTRAINT PK_CustmerRecords_CustomerKey

If you don’t know the name, use the first SELECT Statement to get the Constraint name.

SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'CustmerRecords';  
GO  
-- Delete or Drop
ALTER TABLE [CustmerRecords]  
DROP CONSTRAINT PK_CustmerRecords_CustomerKey

Delete or Drop SQL Primary Key constraint 13

OUTPUT 2

SQL Primary Key Drop constraint query 14

Open the Customer records table in a designer mode to check whether we successfully removed the it on the CustomerKey column or not. As you see, there is no SQL Primary Key constraint symbol besides the CustomerKey column.