在 SQL 表中添加主键列

问题描述：

我是 RDBMS 的学生.

I am student of RDBMS.

我有一个非常基本的问题，假设我在 SQL 服务器中有一个现有的表.改变表的脚本是什么.

I have very basic question let say I have one existing Table in SQL server. What will be script to alter table.

删除列RowId"(如果存在).
如果存在则删除约束.
在表中添加一个新列RowId".
将此列设为主键.
自动增量类型 int.

答

在 SQL Server 2005 或更新版本中，您可以使用此脚本:

In SQL Server 2005 or newer, you could use this script:

-- drop PK constraint if it exists
IF EXISTS (SELECT * FROM sys.key_constraints WHERE type = 'PK' AND parent_object_id = OBJECT_ID('dbo.YourTable') AND Name = 'PK_YourTable')
   ALTER TABLE dbo.YourTable
   DROP CONSTRAINT PK_YourTable
GO

-- drop column if it already exists
IF EXISTS (SELECT * FROM sys.columns WHERE Name = 'RowId' AND object_id = OBJECT_ID('dbo.YourTable'))
    ALTER TABLE dbo.YourTable DROP COLUMN RowId
GO

-- add new "RowId" column, make it IDENTITY (= auto-incrementing)
ALTER TABLE dbo.YourTable 
ADD RowId INT IDENTITY(1,1)
GO

-- add new primary key constraint on new column   
ALTER TABLE dbo.YourTable 
ADD CONSTRAINT PK_YourTable
PRIMARY KEY CLUSTERED (RowId)
GO

当然，如果其他表使用外键约束引用此 dbo.YourTable 到预先存在的 RowId 列，则该脚本可能仍然会失败...

Of course, this script may still fail, if other tables are referencing this dbo.YourTable using foreign key constraints onto the pre-existing RowId column...

更新:和当然，在我使用 dbo.YourTable 或 PK_YourTable 的任何地方，你必须替换那些具有实际表/约束名称的占位符来自您自己的数据库(您在问题中没有提到它们是什么......)

Update: and of course, anywhere I use dbo.YourTable or PK_YourTable, you have to replace those placeholder with the actual table / constraint names from your own database (you didn't mention what they were, in your question.....)

在 SQL 表中添加主键列

相关推荐