Alter NVARCHAR column from NULL to NOT NULL in sql server

Question

I've created a table column called RateValue with initially allowing NULL value but I want to make it NOT NULL. I used SQL Server 2008 R2

I tried the following but it does not work

ALTER TABLE dbo.AAElement
ALTER COLUMN RateValue NVARCHAR(50) NOT NULL

Do you have any rows in this table? If you do you must first make sure that you have no row with a NULL value in RateValue. — Giannis Paraskevopoulos
– Giannis Paraskevopoulos, Commented Aug 28, 2013 at 12:34
"Not working" usually means "you did not read the message". Read it because it tells you what is wrong. — usr
– usr, Commented Aug 28, 2013 at 12:44

Hart CO · Accepted Answer · 2013-08-28 12:38:19Z

4

The most likely problem is pre-existing NULL values.

Get rid of the NULL values first, then alter:

UPDATE AAElement 
SET RateValue = ''
WHERE RateValue IS NULL
GO
ALTER TABLE dbo.AAElement
ALTER COLUMN RateValue NVARCHAR(50) NOT NULL

Otherwise the constraint is violated as it's being created.

answered Aug 28, 2013 at 12:38

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1