Tags: changing, column, database, default, existing, instead, isinserted, microsoft, mysql, oracle, populates, server, sql, value, zero

Changing default value for a column

On Database » Microsoft SQL Server

1,465 words with 1 Comments; publish: Fri, 23 May 2008 14:31:00 GMT; (25062.99, « »)

I have an existing database and I want to change it so that if no value is

inserted into a column that it populates that column with a zero instead of

a

null value. How do I make this change? I suspect I need to change all the

existing nulls to zero before making the change? Any help would be greatly

appreciated.

All Comments

Leave a comment...

  • 1 Comments
    • That depends. Are you happy with the existing nulls? If so, simply add a

      default constraint:

      alter table MyTable

      add

      constraint DF1_MyTable default (0) for MyColumn

      However, if you need to keep all nulls out, then populate the null columns

      with some value and then alter the column:

      alter table MyTable

      alter column

      MyColumn int not null

      go

      Then, add the default constraint as shown above.

      Tom

      ----

      Thomas A. Moreau, BSc, PhD, MCSE, MCDBA

      SQL Server MVP

      Columnist, SQL Server Professional

      Toronto, ON Canada

      www.pinpub.com

      .

      "rk rider" <rkrider.sqlserver.todaysummary.com.discussions.microsoft.com> wrote in message

      news:722BC848-15EA-474D-8F40-6BB078F7CDF3.sqlserver.todaysummary.com.microsoft.com...

      I have an existing database and I want to change it so that if no value is

      inserted into a column that it populates that column with a zero instead of

      a

      null value. How do I make this change? I suspect I need to change all the

      existing nulls to zero before making the change? Any help would be greatly

      appreciated.

      #1; Fri, 23 May 2008 14:32:00 GMT