How to alter an existing column in a table to become a primary key/identity column?


Imagine the following Data Definition Language (DDL):






USE [test]


GO


/****** Objekt:  Table [dbo].[Customer]    Skriptdatum: 12/18/2007 11:04:16 ******/


SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


CREATE TABLE [dbo].[Customer](


      [ID] [int] NOT NULL,


      [SomeText] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL


) ON [PRIMARY]


To alter the column ID to become the primary key/identity column use this sql script






/* Überprüfen Sie das Skript ausführlich, bevor Sie es außerhalb des Datenbank-Designer-Kontexts ausführen, um potenzielle Datenverluste zu vermeiden.*/


BEGIN TRANSACTION


SET QUOTED_IDENTIFIER ON


SET ARITHABORT ON


SET NUMERIC_ROUNDABORT OFF


SET CONCAT_NULL_YIELDS_NULL ON


SET ANSI_NULLS ON


SET ANSI_PADDING ON


SET ANSI_WARNINGS ON


COMMIT


BEGIN TRANSACTION


GO


CREATE TABLE dbo.Tmp_Customer


      (


      ID int NOT NULL IDENTITY (1, 1),


      SomeText nvarchar(50) NOT NULL


      )  ON [PRIMARY]


GO


SET IDENTITY_INSERT dbo.Tmp_Customer ON


GO


IF EXISTS(SELECT * FROM dbo.Customer)


       EXEC(‘INSERT INTO dbo.Tmp_Customer (ID, SomeText)


            SELECT ID, SomeText FROM dbo.Customer WITH (HOLDLOCK TABLOCKX)’)


GO


SET IDENTITY_INSERT dbo.Tmp_Customer OFF


GO


DROP TABLE dbo.Customer


GO


EXECUTE sp_rename N‘dbo.Tmp_Customer’, N‘Customer’, ‘OBJECT’


GO


ALTER TABLE dbo.Customer ADD CONSTRAINT


      PK_Customer PRIMARY KEY CLUSTERED


      (


      ID


      ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


 


GO


COMMIT


Obtaining the above script is easier that you might think. Open the table, make the modifications and click the script changes button( ). Thereupon you will be presented with a dialog holding the desired script.



   Daniel

Comments (3)

  1. WindowsDave says:

    I think that I should give a try to this. But is it not that simple as it seems to be after reading this post.

  2. Mischa Kroon says:

    whats wrong with:

    ALTER TABLE tablename ADD CONSTRAINT

    PK_tablename PRIMARY KEY CLUSTERED

    (ID)

    Way shorter, and more readible 🙂

  3. Hi Mischa,

    using ALTER TABLE the way you described would >>only<< set a primary clustered key on [ID]. All the mumbo-jumbo is necessary to generate the identity field.

    Thanks

      Daniel