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

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

  • Comments 3

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

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • 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.

  • whats wrong with:

    ALTER TABLE tablename ADD CONSTRAINT

    PK_tablename PRIMARY KEY CLUSTERED

    (ID)

    Way shorter, and more readible :)

  • 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

Page 1 of 1 (3 items)