Stop allowing null in a table column

Question

I have a table in SQL server 2008. I need a column that until now wasn't necessary, not to allow NULL anymore, without droping the table. I tried to do something like this:

ALTER TABLE [Sessions] ALTER COLUMN region_id NOT NULL;

EDIT: It was a small syntax error. solved.

_"I tried to do something like this"_ ... and what happened? — Tim Schmelter, Mar 14 '14 at 11:49
You wouldn't have got the answer you got if you included the syntax error message you got in the question. — , Mar 14 '14 at 11:54
No, that most definitely is *not* the message you get for running the query you have in the question. That is the message you get when attempting to change the nullability using the table designer of SSMS. — , Mar 14 '14 at 11:57

score 2 · Answer 1 · answered Mar 14 '14 at 11:56

2

You have to specify the data type as well when you alter a column:

ALTER TABLE [Sessions] ALTER COLUMN region_id int /* ? */ NOT NULL;

answered Mar 14 '14 at 11:56

Damien_The_Unbeliever

234,701
27
340
448

Giannis Paraskevopoulos · Answer 2 · 2014-03-14T11:58:21.120

1

You have to first set all values that are NULL to a non NULL value:

UPDATE [Sessions] 
SET region_id=-1
WHERE region_id IS NULL

Instead of -1 you should choose something that would represent the formerly NULL values so you may distinguish them.

To get away with saving prevention you may go on:

Tools>Options>Designers> and unclick Prevent Saving Changes that require table re-creation

edited Mar 14 '14 at 11:58

answered Mar 14 '14 at 11:51

Giannis Paraskevopoulos

18,261
1
49
69

What is the message that you get? – Giannis Paraskevopoulos Mar 14 '14 at 11:52
"Saving changes in not permitted. The changes you have made require the following tables to be dropped and re-created" – Tomer Amir Mar 14 '14 at 11:55
you should specify the type of region_id column in your alter column script – Sargis Koshkaryan Mar 14 '14 at 11:56

score 1 · Accepted Answer · answered Mar 14 '14 at 11:56

1

you are missing datatype.

ALTER TABLE [Sessions] ALTER COLUMN region_id int NOT NULL;

answered Mar 14 '14 at 11:56

KumarHarsh

5,046
1
18
22

score 1 · Answer 4 · answered Mar 14 '14 at 11:59

First off to save table chances like this you need to either script the changes, or when saving from the UI, you need to set SSMS to drop and recreate table for you. To do this;

Go to Tools Options Designers UNTICK prevent saving changes that require table re-creations.

If you script the changes, you will need to first alter the table, and add a new column allowing nulls. Then update the column and remove nulls and then after you will be able to set the column to NOT NULL.

If you follow these steps you will solve your issue.

Stop allowing null in a table column

4 Answers4