SQL Server - how to remove identity column in table

Asked By chitanya chitanya on 07-Oct-06 05:38 AM

i have products table created by some other

it has identity column productid

i am using this table for some other purpose in dot net


there error raising ki  identity set on

i want to remove projectid  as identity column

Remove the IDENTITY property from an existing column - K Pravin Kumar Reddy replied to chitanya chitanya on 07-Oct-06 06:22 AM

hello

check out this

How do I remove the IDENTITY property from an existing column?

http://www.sqlmag.com/Article/ArticleID/22081/sql_server_22081.html# Manager drops the IDENTITY property from an existing column in much the same way that it adds the IDENTITY property, but you might want to consider alternatives to using Enterprise Manager if you never update the OrderID column. For example, when you want to insert your own order IDs rather than setting the IDENTITY column property to generate the order IDs automatically, you can turn on the IDENTITY_INSERT session option on the Orders2 table from the previous Q&A before you insert http://www.sqlmag.com/Article/ArticleID/22081/sql_server_22081.html#. Note that a session option has two main features. The option affects only the session in which you issued the statement (in this case SET IDENTITY_INSERT <table_name> ON). The option lets you insert your own values into the column but not update it.

To allow column updates, you have to drop the column's IDENTITY property. If you remove the property through Enterprise Manager, the code that Enterprise Manager generates creates a new table, http://www.sqlmag.com/Article/ArticleID/22081/sql_server_22081.html# all the data from the original table to the new table, then renames the new table with the original table's name. However, if you prefer a shorter procedure that doesn't involve copying all the original data to another table and dropping the original table, you can write your own shortcut script. The shortcut script that http://www.sqlmag.com/Files/23/22081/Listing_03.txt shows adds a single column, updates its data to the IDENTITY column values, then drops the IDENTITY column. . . .

reference

http://www.sqlmag.com/Article/ArticleID/22081/sql_server_22081.html

 

code here

LISTING 3: 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)



#articleQuickSubWrapper { width: 590px; background-color: #FFFFFF; text-align: left; } #articleQuickSubWrapper img { float: left; margin-top: 10px; } #articleQuickSubWrapper #theForm { width: 440px; float: right; margin-top: 25px; } #articleQuickSubWrapper #theForm * { font-size: 11px; } #articleQuickSubWrapper #theForm label { width: 90px; display: -moz-inline-box; display: inline-block; text-align: right; } #articleQuickSubWrapper #theForm input, #articleQuickSubWrapper #theForm select { width: 115px; } #articleQuickSubWrapper #theForm input#theFormSubmit { width: auto; margin-top: 10px; } #articleQuickSubWrapper #theForm p { margin-top: 10px; font-size: 11px; text-align: center; }

set identity_insert off - Shallu Gupta replied to chitanya chitanya on 09-Oct-06 12:08 AM

Hi,

What i understood from your problem is yu have set identity_insert on for table products and are trying to insert a row in table products without supplying productid i.e. without supplying value to identity column.

If you want that productid should automatically take the identiy value then you should set identity_insert off for the table product and then try to insert a row in product table without supplying product id..

as

set identity_insert products off

insert into products (col1,col2)

values('val1','val2')