Adding Primary Key to Existing Table

If you want SQL Server to automatically provide values for the new column, make it an identity.

ALTER TABLE Product_Details DROP COLUMN Product_Detail_ID
GO
ALTER TABLE Product_Details ADD Product_Detail_ID int identity(1,1) not null
GO
ALTER TABLE Product_Details
add CONSTRAINT pk_Product_Detils_Product_Detail_ID primary key(Product_Detail_ID)
GO

Note: this will add a new column at the end of the table. You will need to recreate the table if you want the new PK column to be the first column. There is a way around this using the Designer Tool.

https://www.mssqltips.com/sqlservertip/5753/adding-a-primary-key-to-a-prepopulated-table-using-sql-server-object-explorer-in-ssdt/

There are 2 ways to fix existing data error issue:

• clean up the existing data before adding the constraint
• add the constraint with the “WITH NOCHECK” option, this will stop sql server checking existing data, only new data will be checked

Example

ALTER TABLE dbo.tblTable01
ADD CONSTRAINT PK_tblTable01_Col01 PRIMARY KEY(Col01)

Cannot define PRIMARY KEY constraint on nullable column in table ‘tblTable01’.

ALTER TABLE
  dbo.tblTable01
ALTER COLUMN
  Col01
    BIGINT NOT NULL;

Cluster Index automatically created. Statistics automatically created.

Add new column as Primary Key column. Need to repopulate column first then create the PK.

ALTER TABLE dbo.tblTable01
ADD Col04 BIGINT IDENTITY(1,1)
GO

ALTER TABLE dbo.tblTable01
ADD CONSTRAINT PK_tblTable01_Col04
PRIMARY KEY CLUSTERED (Col04)
GO

SQL Compare

If Primary Key column already exist.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO

BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[tblTable01]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[tblTable01] ALTER COLUMN [Col01] [bigint] NOT NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_tblTable01_Col01] on [dbo].[tblTable01]'
GO
ALTER TABLE [dbo].[tblTable01] ADD CONSTRAINT [PK_tblTable01_Col01] PRIMARY KEY CLUSTERED  ([Col01])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO

If Primary Key column as new column.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO

BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[tblTable01]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[tblTable01] ADD
[Col04] [bigint] NOT NULL IDENTITY(1, 1)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_tblTable01_Col04] on [dbo].[tblTable01]'
GO
ALTER TABLE [dbo].[tblTable01] ADD CONSTRAINT [PK_tblTable01_Col04] PRIMARY KEY CLUSTERED  ([Col04])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO

Note: SQL Compare by default will ignore column ordering unless you check the “Force column order” property in the Options tab.

If “Force column order” property is checked, SQL compare will drop and recreate the table.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO

BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Rebuilding [dbo].[tblTable01]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[RG_Recovery_1_tblTable01]
(
[Col04] [bigint] NOT NULL IDENTITY(1, 1),
[Col01] [bigint] NULL,
[Col02] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Col03] [float] NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
INSERT INTO [dbo].[RG_Recovery_1_tblTable01]([Col01], [Col02], [Col03]) SELECT [Col01], [Col02], [Col03] FROM [dbo].[tblTable01]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DROP TABLE [dbo].[tblTable01]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_rename N'[dbo].[RG_Recovery_1_tblTable01]', N'tblTable01', N'OBJECT'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_tblTable01_Col04] on [dbo].[tblTable01]'
GO
ALTER TABLE [dbo].[tblTable01] ADD CONSTRAINT [PK_tblTable01_Col04] PRIMARY KEY CLUSTERED  ([Col04])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO

Sources: