本文介绍了更改表中的PrimaryKey(约束)时出错的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

更改表中的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(约束)时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-19 06:18