将列更改为不允许空值 [英] Change a column to not allow nulls

问题描述

所以我想将我的 SQL Server 数据库中的一列更改为不允许空值，但我一直收到错误消息.这是我正在使用的 sql 语句:

So I want to change a column in my SQL Server database to not allow nulls, but I keep getting an error. this is the sql statement I am using:

alter table [dbo].[mydatabase] alter column WeekInt int not null

这是我得到的错误:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'WeekInt', table 'CustomerRadar.dbo.tblRWCampaignMessages'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

我很确定我的 sql 是正确的，并且我正在尝试更改的列中当前没有空值，所以我真的不确定是什么导致了问题.有任何想法吗?我被难住了.

I'm pretty sure my sql is right, and there are no nulls currently in the column I am trying to change so I am really not sure as to what is causing the problem. Any ideas? I'm stumped.

推荐答案

显然，该表中有 NULL 值.您可以检查:

Clearly, the table has NULL values in it. Which you can check with:

select *
from mydatabase
where WeekInt is NULL;

然后，您可以做两件事之一.要么更改值:

Then, you can do one of two things. Either change the values:

update mydatabase
    set WeekInt = -1
    where WeekInt is null;

或删除违规行:

delete from mydatabase
    where WeekInt is null;

然后，当所有值都正常时，您可以执行alter table 语句.

Then, when all the values are okay, you can do the alter table statement.

这篇关于将列更改为不允许空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！