更改表中的PrimaryKey（约束）时出错 [英] Error when changing PrimaryKey (constraint) in table

问题描述

更改表中的PrimaryKey时出现问题。

Problem when changing PrimaryKey in table.

错误：无法在可为空的列上创建IDENTITY属性。

Error: "Could not create IDENTITY attribute on nullable column."

详细信息：

• 更新是可枚举的类，其中源自DataSourceObject和IClonable


• 表是源自DataSourceObject的类

代码：

foreach (var u in update)
{
   myList.Add(string.Format("alter table [{0}] alter column {1} {2} {3};", table.Name, u.Name, (u.IsPrimary || u.IsAutoIncrement) ? "not null" : "null"));
}

我也尝试过删除所有这样的PrimaryKeys：

I have also tried removing all the PrimaryKeys like this:

foreach (var u in update.Where(x => x.IsPrimary))
{    
   myList.Add(string.Format("alter table [{0}] drop constraint {1}", table.Name, u.Name));
}

但是它表示我要设置为PrimaryKey的当前项不是

But it says that current item I'm trying to set as PrimaryKey is not a constraint.

我也提到过，如果我以这种方式更改 if 语句（反之亦然）：

Also I have mentioned that if I change the if statement this way (vice versa):

（u.IsPrimary || u.IsAutoIncrement）吗？ null： not null;

当我尝试改回主键时，它可以工作。我的意思是，如果我先更改主键，那么会有一个异常，然后我更改if语句，然后再更改PrimaryKey来完成业务。

when I'm trying to change back primary key, it works. I mean, if I change primary key first, then there's an exception, then I change if statement, then change PrimaryKey it does the business.

更新：现在，我绝对知道我必须找到一个要删除的约束名称，在我的情况下，它将是默认名称-ID（可以通过sys schema完成，但我不知道如何使用它），那么我必须删除它，毕竟我必须生成一个具有新名称的新约束。

Update: Now I definitely know that I have to find a constraint name which I want to remove, in my case it will be the default one - ID (it can be done with sys schema, but I don't know how to use it), then I have to drop it, and after all I have to generate a new constraint with new name.

推荐答案

结果查询：

declare @CONSTRAINTNAME varchar(128);
declare @script nvarchar(max);

select top 1 @CONSTRAINTNAME = t1.CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
where CONSTRAINT_TYPE = 'PRIMARY KEY' and upper(t1.TABLE_NAME) = @tableName;

set @script = N'alter table [' + @tableName + N'] drop constraint [' + @CONSTRAINTNAME + N']';
exec sp_executesql @script

set @script = N'alter table [' + @tableName + N'] add constraint [' + @tableName + '_' + @columnName + N'] primary key (['+ @columnName +'])';
exec sp_executesql @script

这篇关于更改表中的PrimaryKey（约束）时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！