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, Aug 28 '13 at 12:34
possible duplicate of [Altering a column: null to not null](http://stackoverflow.com/questions/689746/altering-a-column-null-to-not-null) — Tchami, Aug 28 '13 at 12:38
"Not working" usually means "you did not read the message". Read it because it tells you what is wrong. — usr, Aug 28 '13 at 12:44

score 4 · Accepted Answer · answered Aug 28 '13 at 12:38

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.

1 Answers1