Removing incremental identity integer datatype from a column
Hi,
I want to remove the identity function of auto incrementing the primary key. Taking into consideration that this column is being referenced from several tables. I need the solution to be coded . Thanks for your help.
Regards
Tamer Khalil

naura paxPosted Oct 31, 2009, 2:05 AM
ALTER TABLE dbo.tableB
DROP CONSTRAINT Fk_tableA_checked; -- col1
. --col2
. -- col3
. -- col4
--and so on. Then drop primary key constraint from your id
ALTER TABLE dbo.tableA
DROP CONSTRAINT pk_id;
-- Then add a column of similar name and copy all data of original column to new one
ALTER TABLE dbo.tableA
Add id1 int
update tableA
set id1=id
-- Then drop id column and rename id1 to id using sp_rename then set it to primary key using Alter Table Add constraint pk_id primary key (id).
after that readd all your foreign key constraints.
Alter table tableB
Add constraint fk_id foreignkey (id) references tableA(id)
hope i'm clear.