如何改变复合主键中varchar的长度？

In MSSQL I have a table created like this:

在MSSQL中，我有一个像这样创建的表：

CREATE TABLE [mytable] (fkid int NOT NULL, data varchar(255) CONSTRAINT DF_mytable_data DEFAULT '' NOT NULL);
ALTER TABLE [mytable] ADD CONSTRAINT PK_mytable_data PRIMARY KEY (fkid, data);

Now I want to increase the length of the 'data' column from 255 to 4000.

现在我想将'data'列的长度从255增加到4000。

If I just try:

如果我尝试：

ALTER TABLE [mytable] ALTER COLUMN data varchar(4000);

Then I get this error:

然后我收到这个错误：

The object 'PK_mytable_data' is dependent on the column 'data'

If I try this:

如果我试试这个：

ALTER TABLE [mytable] DROP CONSTRAINT PK_mytable_data;
ALTER TABLE [mytable] ALTER COLUMN data varchar(4000);
ALTER TABLE [mytable] ADD CONSTRAINT PK_mytable_data PRIMARY KEY (fkid, data);

Then I get this error:

然后我收到这个错误：

Cannot define PRIMARY KEY constraint on nullable column in table 'mytable'

What am I missing? Both columns were defined with NOT NULL, so why is MSSQL reporting that it can't recreate this constraint after I drop it?

我错过了什么？这两列都是用NOT NULL定义的，那么为什么MSSQL报告它丢弃之后无法重新创建这个约束呢？

Thanks! Evan

谢谢！埃文

3 个解决方案

#1

By altering the datatype to varchar(4000), you make it accept NULLs.

通过将数据类型更改为varchar（4000），可以使其接受NULL。

Try this:

尝试这个：

ALTER TABLE [mytable] DROP CONSTRAINT PK_mytable_data;
ALTER TABLE [mytable] ALTER COLUMN data varchar(4000) NOT NULL;
ALTER TABLE [mytable] ADD CONSTRAINT PK_mytable_data PRIMARY KEY (fkid, data);

Note that the index size (which is implicitly create for PK) is limited to 900 bytes and inserts of greater values will fail.

请注意，索引大小（隐式为PK创建）限制为900个字节，更大值的插入将失败。

#2

you don't have to drop the constraint, simply NOCHECK it

你不必删除约束，只需检查它

IF EXISTS 
(SELECT 1 FROM sys.tables tab INNER JOIN sys.columns col ON tab.object_id = col.object_id      WHERE tab.name = 'MY_TABLE' AND col.name = 'MY_COLUMN')

BEGIN

ALTER TABLE MY_TABLE NOCHECK CONSTRAINT ALL
ALTER TABLE [dbo].[MY_TABLE] ALTER COLUMN [MY_COLUMN] VARCHAR(50) NOT NULL;
ALTER TABLE MY_TABLE CHECK CONSTRAINT ALL

END

GO

**note that is only going to work in the 'increase' sense, it does not work for decreasing the size because it could cause primary key constraint violations (think if you had two cells of data AAB and AAC, and you decreased the size by one.) For that case you would have to drop the constraint, but not before you have some sql which will store the data in a temp table check to make sure it's going to fit in your new altered column with no dups, and then update back to the new altered table column.

**请注意，这只会在“增加”意义上起作用，但它不能用于减小大小，因为它可能会导致主键约束违规（如果您有两个数据AAB和AAC单元，并且您减小了大小）一个。）对于这种情况你必须删除约束，但不是在你有一些sql将数据存储在临时表检查，以确保它将适合你的新更改列没有重复，然后更新回新的更改表列。

#3

Do not be surprised if you get a warning when you create this index in the end, you are giving it the potential to create an index key greater than the allowed 900 bytes. (Since the PK will either be the clustered index (default) or an NC index enforcing it.)

如果在最后创建此索引时收到警告，请不要感到惊讶，您可能会创建一个大于允许的900字节的索引键。（因为PK将是聚集索引（默认）或强制执行它的NC索引。）

#1