[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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