SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Alter a column to add "not null"
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DaveBF
Yak Posting Veteran

88 Posts

Posted - 10/30/2013 :  13:27:33  Show Profile  Reply with Quote
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.

Edited by - DaveBF on 10/30/2013 14:04:23

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/30/2013 :  13:29:53  Show Profile  Reply with Quote
try

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


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

Edited by - visakh16 on 10/30/2013 13:30:22
Go to Top of Page

DaveBF
Yak Posting Veteran

88 Posts

Posted - 10/30/2013 :  13:39:42  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/30/2013 :  14:10:55  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/31/2013 :  02:57:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000