如何改变复合主键中的varchar长度? [英] How to alter length of varchar in composite primary key?
问题描述
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);
现在我想将'data'列的长度从255增加到4000.
如果我只是尝试:
ALTER TABLE [mytable] ALTER COLUMN data varchar (4000);
然后我得到这个错误:
对象'PK_mytable_data'依赖于列'data'
如果我尝试这样:
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);
然后我得到这个错误:
无法在表'mytable'中的可空列上定义PRIMARY KEY约束
我缺少什么?两个列都用NOT NULL定义,为什么MSSQL报告它在我删除后不能重新创建这个约束?
谢谢!
Evan
通过将数据类型更改为 varchar(4000)
,您可以接受 NULLs
。
尝试:
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);
请注意,索引大小(隐式创建 PK
)限制为 900
字节,并且较大值的插入将失败。
In MSSQL I have a table created like this:
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.
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?
Thanks! Evan
By altering the datatype to varchar(4000)
, you make it accept NULLs
.
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.
这篇关于如何改变复合主键中的varchar长度?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!