将表主键列大小更改为255(Alter table primary key column size to 255)

将表主键列大小更改为255(Alter table primary key column size to 255)

我有一个主键列大小设置为50的表。由于一些新的要求，我需要增加到255的大小。在网上搜索后，我明白它是不可能改变列，如果它是一部分主键约束。 所以，我采取了放弃约束，改变列和添加约束的方法。 但是，我仍然遇到一个小问题，我的原始列是Non Null类型，并且具有默认值集，但现在当我尝试以下SQL时，出现错误“DEFAULT附近的语法不正确”

ALTER TABLE [tblLocation] DROP CONSTRAINT [PK_tblLocation] ALTER TABLE [tblLocation] ALTER COLUMN Location VARCHAR(255) DEFAULT('New Location') NOT NULL ALTER TABLE [tblLocation] ADD CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED ( [Location] ASC )

谢谢你的帮助。 Javid

I have a table with a primary key column size is set to 50. Due to some new requirements, I need to increase the size to 255. After searching online, I understood it is not possible to alter a column if it is a part of a primary key constraint. So, I took the approach of dropping the constraint, altering the column and adding the constraint back. But, I am still experiencing a small issue, my original column is of type Non Null and has a default value set, but now when i try the following sql, I get an error "Incorrect syntax near DEFAULT"

ALTER TABLE [tblLocation] DROP CONSTRAINT [PK_tblLocation] ALTER TABLE [tblLocation] ALTER COLUMN Location VARCHAR(255) DEFAULT('New Location') NOT NULL ALTER TABLE [tblLocation] ADD CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED ( [Location] ASC )

Thanks for any help. Javid

最满意答案

您不能使用`ALTER TABLE ... ALTER COLUMN'同时修改列和约束（这里是默认值）。 您首先需要更改列，然后更改默认约束。 （您可能需要先删除然后重新创建默认设置。）

You cannot use `ALTER TABLE... ALTER COLUMN' to modify both a column and a constraint (here, the default) at the same time. You will first need to alter the column, and then alter the default constraint. (You might need to first drop and then recreate the default.)