[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Programmatically add default to column

I have a table MyTable with a column MyColumn, which is defined as BIT and currently has no default value specified.

What SQL Syntax do I use to add a Default of 0 to that column?

I have tried ALTER TABLE MyTable ALTER COLUMN MyColumn DEFAULT 0;  which does not work.
0
wsturdev
Asked:
wsturdev
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This should help you out:

ALTER TABLE MyTable ALTER COLUMN MyColumn SET DEFAULT 0;
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello wsturdev,

ALTER TABLE dbo.urTableb
ADD CONSTRAINT       DF_urConstraintName  DEFAULT 'DefaultValue' FOR ColumnName

Regards,

Aneesh
0
 
Tapan PattanaikSenior EngineerCommented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If that didn't work then try creating a Default constraint as given below which would do the equivalent operation:

ALTER TABLE MyTable ADD CONSTRAINT def_constraint DEFAULT 0 FOR MyColumn;
0
 
Tapan PattanaikSenior EngineerCommented:
hi wsturdev,

                Check this one.


http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20726441.html
ALTER TABLE MyTable ADD CONSTRANT  DF_ConstrantName DEFAULT 0 FOR MyColumn

Open in new window

0
 
Lee R Liddick JrReporting AnalystCommented:
Sometimes you need to delete the prior default constraint that is automatically created by SQL Server...see this article:  http://bypsoft.blogspot.com/2007/10/changing-default-column-values-sql.html
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now