x

alter column with default value

alter table enquiry alter column RejectReson int default 0

I want alter the column data type with default value zero,but it is not happening.

more ▼

asked Sep 27, 2017 at 10:26 AM in Default

avatar image

sandeep_121
1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You need to first create the default constraint on its own, then bind the constraint to the column.

The only time DEFAULT 0 works is on CREATE not ALTER

more ▼

answered Sep 27, 2017 at 10:46 AM

avatar image

anthony.green
3.1k 1 4 6

(comments are locked)
10|1200 characters needed characters left

If the column RejectReson did not exist yet then you could add the column and its respective default specification at the same time as @anthony.green already pointed out in his answer. When adding a NULLable column to existing table and yet requiring that all existing rows to have the default value already set for this new column then WITH VALUES can be specified, i.e.

 alter table enquiry add RejectReson int null default(0) with values;
 go

The statement above would add new NULLable column and populate all existing rows with default value of 0 for this new column.

If the newly created column mustn't allow NULL then WITH VALUES is not necessary as adding the default while creating NOT NULLable column will do the trick, i.e. all existing rows will come in populated with default value:

 alter table enquiry add RejectReson int not null default(0);
 go

However, because the column already exist (I presume it is NULLable), adding the default to it will not automatically populate existing rows with default value for this existing column. Adding default will affect the rows which will be inserted in the future.

Coming back to the original question: the syntax is incorrect, and also there is no way to add a default to existing NULLable column and force all rows to be automatically updated. In order to achieve the desired result, please run the following script:

 alter table enquiry add default(0) for RejectReson;
 go
 update enquiry set RejectReson = 0;
 go

Now, the default has been added, all existing rows have default value, the column is NULLable, and yet all newly inserted rows will come in with default value (unless explicitly specified otherwise).

Hope this helps.

Oleg

more ▼

answered Sep 27, 2017 at 02:50 PM

avatar image

Oleg
20.6k 3 7 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x2036
x512
x428
x145

asked: Sep 27, 2017 at 10:26 AM

Seen: 123 times

Last Updated: Sep 27, 2017 at 02:50 PM

Copyright 2018 Redgate Software. Privacy Policy