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 to Add PK to existing Table. ALTER TABLE schema.Tablename ADD CONSTRAINT PrimaryKeyContaintName PRIMARY KEY CLUSTERED (Column1,Column2....);
-- 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.
-- 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
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.