Change a column to not allow nulls

Question

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.

There error seems to indicate you are running an `INSERT` or `UPDATE` statement, not an `ALTER TABLE` statement. — , Aug 05 '13 at 22:16
are you 110% sure that there aren't any `nulls` in that column, the error would suggest there are. — T I, Aug 05 '13 at 22:21
@TI just doubled checked and there are a few, cant believe I missed that. Will rectify it and get back to you — user2255811, Aug 05 '13 at 23:04
Nothing wrong with that statement. Definitely a case of rogue NULLs. Probably why you want to add the constraint. =) — Eric Hauenstein, Aug 05 '13 at 23:21

score 18 · Accepted Answer · answered Aug 06 '13 at 00:11

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.

score 1 · Answer 2 · answered Feb 04 '19 at 12:57

1

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.

answered Feb 04 '19 at 12:57

Greg Gum

33,478
39
162
233

That one got me too. Nice catch. – PongGod Mar 12 '20 at 15:26

score 0 · Answer 3 · answered May 27 '21 at 16:26

0

This will work. You should send a default value, then it will change all the previous record to -1 in this example.

alter table [dbo].[mydatabase] alter column WeekInt int not null DEFAULT '-1';

answered May 27 '21 at 16:26

akshay bhoendie

273
3
10

Change a column to not allow nulls

3 Answers3

Linked

Related