如何禁用表中列的标识 [英] how to disable is identity of a column in table

问题描述

如何通过sql querry plz help禁用表中列的标识

how to disable is identity of a column in table with the help of sql querry plz help

推荐答案

我认为这将为您提供帮助:

I think this will help you:

SET IDENTITY_INSERT TableName ON|OFF

尊敬的维杰(Veary)

上面的代码只会禁用身份，如果您必须完全删除身份，请执行以下步骤.

-如果其他表的外键指向IDENTITY列，则将其删除.在这里，脚本将删除
-指向OrderDetails2中的Orders2.OrderID的外键.
ALTER TABLE OrderDetails2
DROP CONSTRAINT FK_OrderDetails2_Orders2

-如果IDENTITY列上存在主键，请按照以下方式删除主键约束
--script从Orders2删除主键.
ALTER TABLE Orders2
DROP约束PK_Orders2

-将与IDENTITY列相同数据类型的另一列添加到Orders 2，并允许
--NULL.
ALTER TABLE Orders2
ADD new_OrderID int NULL

-使用IDENTITY列的值更新新列.
UPDATE Orders2
SET new_OrderID =订单ID

-如果新列不允许使用NULL，请将列更改为NOT NULL.
ALTER TABLE Orders2
ALTER COLUMN new_OrderID int NOT NULL

-删除IDENTITY列.
ALTER TABLE Orders2
DROP COLUMN OrderID

-将新列重命名为删除的IDENTITY列的名称.
EXEC sp_rename``Orders2.new_OrderID''，``OrderID''，``COLUMN''

-如果新列上存在主键，请重新创建该键.在这种情况下，您将重新创建
-Orders2上的主键.
ALTER TABLE Orders2
添加约束PK_Orders2主键(OrderID)

-在其他表上，重新创建最初指向旧IDENTITY列的所有外键
-将它们指向新列.在这里，您可以在OrderDetails2上重新创建外键.
带有NOCHECK的ALTER TABLE OrderDetails2
添加约束FK_OrderDetails2_Orders2
外键(OrderID)
参考Orders2(OrderID)

Dear Vijay

The above code will only disable the identity, if you have to remove the identity altogether there is the steps below.

--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)

SET IDENTITY_insert  tbbranchheadoffice on
insert into tbbranchheadoffice(id,name,dob,fathersname)values(85,'Vk','dsfsd','dsfs')

这篇关于如何禁用表中列的标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！