在 SQL 表中添加主键列 [英] Add primary key column in SQL table
问题描述
我是 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 表中添加主键列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!