如何更改 SQL Server 中主键的列长度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30350822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change the column length of a primary key in SQL Server?
提问by LearnByReading
I know how to change the length of a column, but my SQL statement fails because the column I'm trying to change is a PK, so I get the following error:
我知道如何更改列的长度，但是我的 SQL 语句失败了，因为我要更改的列是 PK，因此出现以下错误：
Msg 5074, Level 16, State 1, Line 1
The object 'PK_TableName' is dependent on column 'PersonID'.
消息 5074，级别 16，状态 1，第 1 行
对象“PK_TableName”依赖于列“PersonID”。
PersonID = PK.
人员 ID = PK。
I've read What is the sql to change the field length of a table column in sql serverwhich only applies to non-PK columns.
我已经阅读了什么是 sql 来更改仅适用于非 PK 列的sql server 中表列的字段长度。
I tried this:
我试过这个：
ALTER TABLE table_name
ALTER COLUMN column_name <new datatype>
回答by Indra Prakash Tiwari
See below sample example how to increase size of the primary column
请参阅下面的示例示例如何增加主列的大小
Create a sample table
create table abc (id varchar(10) primary key)Find primary constraint in key constraints tables
select object_name(object_id),* from sys.key_constraints where object_name(parent_object_id) = 'abcDrop constraint
ALTER TABLE abc DROP CONSTRAINT PK__abc__3213E83F74EAC69B(Replace PK__abc__3213E83F74EAC69B with constraint name you receive.)
Add not null
ALTER TABLE abc alter column id varchar(20) NOT NULL;Add primary key again
ALTER TABLE abc ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (id)
创建示例表
create table abc (id varchar(10) primary key)在关键约束表中查找主要约束
select object_name(object_id),* from sys.key_constraints where object_name(parent_object_id) = 'abc丢弃约束
ALTER TABLE abc DROP CONSTRAINT PK__abc__3213E83F74EAC69B（将 PK__abc__3213E83F74EAC69B 替换为您收到的约束名称。）
添加不为空
ALTER TABLE abc alter column id varchar(20) NOT NULL;再次添加主键
ALTER TABLE abc ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (id)
回答by The Reason
ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>
ALTER TABLE table_name
ALTER COLUMN column_name datatype
ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)
回答by Wendel
SQLServer 2008 did not allow me to change a primary key with data so I deactivated all the constraints, performed the command and activated all the constraints again. The commands are:
SQLServer 2008 不允许我使用数据更改主键，因此我停用了所有约束，执行命令并再次激活所有约束。命令是：
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
-- commands here
EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"
