HOW TO REMOVE Primary Key IDENTITY COLUMN From A table

Question

HOW TO REMOVE Primary Key IDENTITY COLUMN From A table

Ivan Mohapatra

SSCarpal Tunnel

Points: 4452
More actions
November 18, 2011 at 5:46 am

#144662

Hi all,
I have a Table
name int Pk Identity(1,1)
job varchar(20)
how i can remove the Identity property from the column.
well don'nt ask to delete the table or drop the table.- which i know and it is the last option.
all suggestion are well come.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 1

You can't alter that out of a table. So, you have to create a second table with the structure you want, copy the data across, drop the original table, rename the new table, add any foreign key constraints to the new table.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Drop the column and re-create it.

Here's an example:

CREATE TABLE #test (

id int identity(1,1) PRIMARY KEY,

name nvarchar(50)

);

ALTER TABLE #test ADD new_id int NOT NULL; -- Remember NOT NULL, otherwise you

-- won't be able to set it as the PK later

UPDATE #test SET new_id = id;

DECLARE @name sysname

SELECT @name = name

FROM tempdb.sys.key_constraints

WHERE parent_object_id = OBJECT_ID('tempdb..#test')

DECLARE @sql nvarchar(max)

SET @sql = 'ALTER TABLE #test DROP CONSTRAINT ' + @name

EXEC(@sql)

ALTER TABLE #test DROP column id

EXEC tempdb.sys.sp_rename '#test.new_id', 'id', 'COLUMN'

ALTER TABLE #test ADD CONSTRAINT PK_test PRIMARY KEY(id)

Since I created the example for a #temp table, everything that references tempdb in that script must be changed to your database name.

Hope this helps

Gianluca

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Almost forgotten: wrap that code into a transaction!!!!

-- Gianluca Sartori

Ivan Mohapatra SSCarpal Tunnel Points: 4452 More actions · Answer 4

i can do it but

If i have a foreign key related to My Primary key how can i do it

and i don'nt have data in those table it is a empty table

so i just want to remove the db and recreate the db and table by

script the db and edit the required change.

and create every thing new

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Gianluca Sartori (11/18/2011)
Almost forgotten: wrap that code into a transaction!!!!

Less risk when doing that on the test server.

You're going to run that on the test server first? Right?

Then backup prod before running the staement IN transaction.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Ivan Mohapatra (11/18/2011)
If i have a foreign key related to My Primary key how can i do it ...

By dropping the child table FK... dropping/recreating your Parent table PK... recreating your child table FK pointing to the Parent table perhaps?

Please note you may have to work on your child tables to be sure you have the columns needed to actually build FKs against the new Parent table PK

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Ivan Mohapatra SSCarpal Tunnel Points: 4452 More actions · Answer 7

The Solution which i found out is

Script the both Pk table And FK table and drop those table and recreate it with one shot .As for this there is a option in the Object explorer > Table > right click> script> Drop And create.

I did IT in this way.

And IT worked .