能否在不删除和重新创建表以及丢失所有数据的情况下,在SQL Server表上设置主键和自动增量?
能否在不删除和重新创建表以及丢失所有数据的情况下,在SQL Server表上设置主键和自动增量?
当然可以!您只需要添加一个新列,并将其设置为INT IDENTITY
,然后添加主键约束:
ALTER TABLE dbo.YourTable
ADD ID INT IDENTITY(1,1) NOT NULL
ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable PRIMARY KEY (ID)
IF EXISTS (SELECT * FROM sys.key_constraints
WHERE type = 'PK' AND parent_object_id = OBJECT_ID('MyTable')
AND Name = 'PK_MyTable')
ALTER TABLE MyTable DROP CONSTRAINT PK_MyTable
ALTER TABLE MyTable ADD MyKey INT IDENTITY
ALTER TABLE MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY(MyKey)