Removing identity from a column

Question

Hi, I want to remove the identity from a primary key column, that has several tables referenced to it. Regards

Lalit M · Answer

Remove or change identity from a primary key column. visit this link http://blog.ancheril.net/2009/09/change-the-primary-key-column-to-identity-field-sql/

jagdish bhoir · Answer

Code That Drops the IDENTITY Property from an Existing Column --If other tables' foreign keys point to the IDENTITY column, drop them. Here, the script drops --the foreign key pointing to Orders2.OrderID from OrderDetails2. ALTER TABLE OrderDetails2 DROP CONSTRAINT FK_OrderDetails2_Orders2 --If a primary key exists on the IDENTITY column, drop the primary key constraint the way this --script drops the primary key from Orders2. ALTER TABLE Orders2 DROP Constraint PK_Orders2 --Add another column with the same data type as the IDENTITY column to Orders 2 and allow --NULLs. ALTER TABLE Orders2 ADD new_OrderID int NULL --Update the new column with the values of the IDENTITY column. UPDATE Orders2 SET new_OrderID = OrderID --If the new column doesn't permit NULLs, alter the column to NOT NULL. ALTER TABLE Orders2 ALTER COLUMN new_OrderID int NOT NULL --Drop the IDENTITY column. ALTER TABLE Orders2 DROP COLUMN OrderID --Rename the new column to the dropped IDENTITY column's name. EXEC sp_rename 'Orders2.new_OrderID', 'OrderID', 'COLUMN' --If a primary key exists on the new column, recreate the key. In this case you recreate the --primary key on Orders2. ALTER TABLE Orders2 ADD CONSTRAINT PK_Orders2 PRIMARY KEY(OrderID) --On other tables, recreate any foreign keys that originally pointed to the old IDENTITY column --and point them to the new column. Here, you recreate the foreign key on OrderDetails2. ALTER TABLE OrderDetails2 WITH NOCHECK ADD CONSTRAINT FK_OrderDetails2_Orders2 FOREIGN KEY(OrderID) REFERENCES Orders2(OrderID)

Satyanarayan Bajoria · Answer

Hi, You can go for cascading rule to remove the records from the child table which has reference of the primary table

Kirtan Patel · Answer

Friend, there is no code for this .. if you make the Option Cascade it will automatically delete Referenced Data in Child Tables when you delete parent record :)

Tamer Khalil · Answer

could you send me the code of that?

Kirtan Patel · Answer

Friend, you need to make Delete Rule cascade when you define the Foreign key relation ship in the database table .... like below image Now when you delete primary key data it will automatically delete Foreign key data without bothering you about it :) Friend,if it helps you please check " Do you like this answer "

Removing identity from a column

Answers (6)