Removing IDENTITY Constraint

Question

Post reply

Removing IDENTITY Constraint

Rahul-13112

Old Hand

Points: 328
More actions
February 19, 2004 at 2:47 am

#83831

How can I remove the IDENTITY contraint for a column in my table.
Actually I am using SELECT INTO to move my table(with identity field) into a new table(DT1).
I wanna add a new IDENTITY column to DT1 table. I can only add a new identity column once I have removed the identity constraint from the copied data..
Thus I wanna know how to remove the constraint so that I can add a new identlty constraint

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

don't use select into, but rather create your table first and then insert into like this

if object_id('ident') is not null

drop table ident

if object_id('ident1') is not null

drop table ident1

create table ident(id int identity(1,1),

col1 char(1) default 'a')

create table ident1(id int,

col1 char(1) default 'a')

insert into ident default values

insert into ident1(id, col1) select id, col1 from ident

select * from ident1

select identitycol from ident

--select identitycol from ident1

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Copy and paste with a baby on the arms is more difficult than I thought.

Forgot to copy the last lines. After doing the insert into, you may alter your table and add your identity column. So the complete script might look something like this

if object_id('ident') is not null

drop table ident

if object_id('ident1') is not null

drop table ident1

create table ident(id int identity(1,1),

col1 char(1) default 'a')

create table ident1(id int,

col1 char(1) default 'a')

insert into ident default values

insert into ident1(id, col1) select id, col1 from ident

alter table ident1 add myid int identity

go

select * from ident1

select identitycol from ident1

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

rstone SSCertifiable Points: 6209 More actions · Answer 3

Look in BOL for SET IDENTITY_INSERT. There is info there that should be understood before using this.

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

This will let you insert the values into the identity column, which might be required to restore records.

Also check out DBCC CHECKIDENT. The following should force current identity value to be recalculated. The trick is required because a valid value is not reseeded, even if it leaves a gap.

DBCC CHECKIDENT (jobs, RESEED, 1) -- trick

DBCC CHECKIDENT (jobs, RESEED) -- fix

However, this does not let you update the identity field. I need to update values in an identity column to correct a problem. Does anybody have easy T-SQL that will remove the identity property without dropping the column? It is easy to do via EM, but I would like to include it in the script to correct the data.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Have you ever looked at what EM does behind the scenes when removing the identity property from a column?

Here's an example:

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

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

CREATE TABLE dbo.Tmp_aaa

(

vruconfirmation char(6) NULL,

jobtype char(3) NULL,

jobid char(10) NULL,

id int NOT NULL

&nbsp ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.aaa)

EXEC('INSERT INTO dbo.Tmp_aaa (vruconfirmation, jobtype, jobid, id)

SELECT vruconfirmation, jobtype, jobid, id FROM dbo.aaa TABLOCKX')

GO

DROP TABLE dbo.aaa

GO

EXECUTE sp_rename N'dbo.Tmp_aaa', N'aaa', 'OBJECT'

GO

COMMIT

EM does not only drop the column but rather the whole table

Not really better

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

rstone SSCertifiable Points: 6209 More actions · Answer 5

That's what I expected. I used the VC++ IDE years ago with a database project. It would do the same thing for you. It also let you script the change without execution. It included FKs, etc. Guess it's the manual route for me.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉