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.
asked Sep 27, 2017 at 10:26 AM in Default
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
answered Sep 27, 2017 at 10:46 AM
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.
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:
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:
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.
answered Sep 27, 2017 at 02:50 PM