Alter a column to add "not null"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alter a column to add "not null"

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-10-30 : 13:27:33

Hello.

How do you alter a table to set a column which is currently int, to int not null?

I have already set all values to either 0 or 1.

Why can't I use:
Alter table myTable alter myCol int not null default(0)

I get a syntax error on the word default. If I remove "default(0)" then the command executes ok, but my Inserts don't work because there's no default value.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 13:29:53

try


Alter table myTable alter column myCol int not null default(0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-10-30 : 13:39:42

quote:
Originally posted by visakh16

try

Alter table myTable alter column myCol int not null default(0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

This doesn't work. It gives a syntax error on the word Default. Do I have to explicitly add a constraint?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 14:10:55

quote:
Originally posted by DaveBF

quote:
Originally posted by visakh16

try

Alter table myTable alter column myCol int not null default(0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

This doesn't work. It gives a syntax error on the word Default. Do I have to explicitly add a constraint?

Yep..thats true..just tested this
Seems like only way is to add it as a constraint

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-31 : 02:57:26

It should be noted that you can add a column with default value. However you need to add a constraint to alter a column to have a default value

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources