Change a column to not allow nulls
Solution 1
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;
Or delete the offending rows:
delete from mydatabase
where WeekInt is null;
Then, when all the values are okay, you can do the alter table
statement.
Solution 2
If you are trying to change a column to not null, and you are getting this error message, yet it appears the column has no nulls, ensure you are checking for is null
and not = null
(which gives different results).
Select * from ... where column is null
instead of
Select * from ... where column = null
I am adding this because it tripped me up and took a while to resolve.
user2255811
Updated on May 28, 2021Comments
-
user2255811 about 3 years
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
and this is the error I am getting :
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.
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.
-
PongGod over 4 yearsThat one got me too. Nice catch.