SQL Primary Key

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 Server Primary key columns to uniquely identify the columns and access the data quickly and effectively.

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

• A SQL table can contain Only one Primary key.
• Therefore, you can’t insert any duplicate content into these columns.
• This column doesn’t allow NULL values. By default, NOT NULL will be assigned to these columns, even if you forgot at the creation time.
• 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 this Primary Key constraint using the Transact query and the Management Studio. Let us see how to create SQL Primary Key using a transact query and create a table statement. Here we will define it at the creation time.

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 SQL Primary Key keyword to the Column declaration. SSMS is intelligent enough to create it for you.

See the internal code generated by the SSMS by right-clicking on the table -> script as – > create to new query window option. Refer to Create Table article in SQL Server.

Create SQL Primary Key on the Existing table

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

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 to the above table. For this example, we use 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

Insert rows into the Constraint Column

Let me insert a few rows to check the functionality of the SQL Server primary key constraint.

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 in the column.

Insert Duplicate into Constraint Column

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

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

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

Let me insert the Null Value into the primary key column. For this, we added a 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.

Delete SQL Server Primary Key using DROP Constraint

If you know the constraint name, use the DROP Constraint statement and 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

OUTPUT 2

Open the Customer records table in designer mode to check whether we successfully removed it from the CustomerKey column or not. As you see, there is no Key symbol beside the CustomerKey column.

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

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 Primary Key constraint to the Customerkey column.

Right-click on the SQL 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 using Management Studio

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

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