sql-servernullalter-column

Change a column to not allow nulls


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.


Solution

  • 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.