Share via

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

  • Anonymous
    December 17, 2007
    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.

  • Anonymous
    December 18, 2007
    whats wrong with: ALTER TABLE tablename ADD CONSTRAINT PK_tablename PRIMARY KEY CLUSTERED (ID) Way shorter, and more readible :)

  • Anonymous
    December 18, 2007
    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

  • Last updated on