Create Primary Key on Existing Table in SQL Server

In this article we will discuss two topics about Primary Key

  • How to Create Primary Key on existing Table.
  • How to Insert Identity Column on Existing Table

As we all know that as name says, Primary Key is the Key of table that identifies values under rows of table.

Basics of Primary Key

In SQL Server, Primary key is a single or multiple column combination that contains combined unique value that identifies the row in Table.

It must have unique value in the table, either it is created by a single column or multiple columns.

Any column in PK can not have NULL value

Table can have only one PK Column

Creating Primary Key on Table

To Create a PK Column on Table, SQL User must have permission “CREATE TABLE” and “ALTER” permission on schema in which table is created.

With CREATE TABLE : T-SQL

CREATE TABLE dbo.CustomerTransactions
   (
      ID int IDENTITY (1,1) NOT NULL 
	  ,TrDate Datetime
	  ,Amount Decimal(18,2)
      , CONSTRAINT PK_CustomerTransactions_ID PRIMARY KEY CLUSTERED (ID)
   )
;

In the example above, we created a table with Id Column, Transaction Date and Amount for Customer Transactions. We also added Constraint Primary Key with PK Constraint name and column name (ID)

Syntax :

-- Syntax to Add PK to existing Table.
ALTER TABLE schema.Tablename
   ADD CONSTRAINT PrimaryKeyContaintName PRIMARY KEY CLUSTERED (Column1,Column2....);

Example :

-- Create a Table with ID column but no PK constraint
CREATE TABLE dbo.CustomerTransactions
   (
	    ID int IDENTITY (1,1) NOT NULL 
	,  TrDate Datetime
	  ,Amount Decimal(18,2)
   )
;
-- Add a PK constraint on CustomerTransactions on Existing Column (ID)
ALTER TABLE dbo.CustomerTransactions
   ADD CONSTRAINT PK_CustomerTransaction_ID PRIMARY KEY CLUSTERED (ID);

In the above query we have two separate queries. First creates a table but no primary key. Second query creates a primary key on existing table with its name and Key column

In all the examples we can see that we have ID column with IDENTITY(1,1) property. That defines that ID column is identity column and it should auto increment int value with seed 1 and 1 value increment.

What if we forgot to do so? We do not have identity column in table, now what?

Create IDENTITY INSERT on Existing table.

Sometimes while creating a table we create PK column and set value to integer. But we forgot to set this column IDENTITY.

Remember, we can not alter column to IDENTITY once it is created. But there is workaround there, we will discuss it later in this article.

Lets see an example, where we create a table separately and add Identity column afterwards.

Example :

-- Create a Table without ID column
CREATE TABLE dbo.CustomerTransactions
   (
	     TrDate Datetime
	  ,Amount Decimal(18,2)
   )
;
--Add ID Column with IDENTITY
Alter Table CustomerTransactions Add ID int Identity(1,1)  

Now we know how to create a IDENTITY column on existing table where IDENTITY Column is not exist. It is not allowed to create multiple Identity column on a Table. If we try to add multiple IDENTITY Columns on a Table we get error.

Multiple identity columns specified for table 'CustomerTransactions'. Only one identity column per table is allowed.

Lets see how to Convert Existing Column on a Table into Identity Column.

As we discussed before, there is no method given by SQL to Convert it into Identity. We can drop that column and Create a new one with same name and Identity ON

-- Create a Table with ID column but NO Identity
CREATE TABLE dbo.CustomerTransactions
   (
   ID int NOT NULL 
	     ,TrDate Datetime
	  ,Amount Decimal(18,2)
   )
;
--Add TransactionID Column with IDENTITY
Alter Table CustomerTransactions Add TransactionID int Identity(1,1)  

--DROP Existing ID Column 
Alter Table CustomerTransactions Drop Column Id  

-- Rename New Column TransactionID to ID
Exec sp_rename 'CustomerTransactions.TransactionID', 'Id', 'Column'  

Lets insert few rows to make sure if Identity value is generated automatically or not.

 Insert into CustomerTransactions (TrDate,Amount)
 Select '2020-01-12',2321

Insert into CustomerTransactions (TrDate,Amount)
 Select '2019-08-17',89565

Select * from CustomerTransactions
Primary Key with Identity

Now we are done with Primary key column and Identity column on the table.

Did you know we can also find the ID value of Last Inserted Row

Don’t forget to drop a word in the comment section so we can help you with more useful information.

One thought on “Create Primary Key on Existing Table in SQL Server

  • December 10, 2020 at 11:06 am
    Permalink

    Good entertaining and informative take on a confusing period of European history. More please. Mikaela Roderich Guyon

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *