How to change the Default Column value in sql server

Question

In my table while altering I gave '1' to the default. Now I need to reset the value for the default one, '0'. I tried the following script but it is throwing an error.

ALTER TABLE Order ADD Cancel BIT CONSTRAINT [DF_Order_Cancel] DEFAULT ((1)) NOT NULL;

Here I need to reset the default value to '0' instead of '1'.

I tried the script below but it's still throwing an error.

ALTER TABLE Order ADD DEFAULT (0) FOR Cancel

Answer 1

First, delete the constraint.

alter table Order drop constraint DF_Order_Cancel

Then recreate it.

ALTER TABLE Order ADD DEFAULT 0 FOR Cancel

Edit: the following statements run fine.

ALTER TABLE Order ADD Cancel BIT CONSTRAINT [DF_Order_Cancel] DEFAULT ((1)) NOT NULL;
alter table Order drop constraint DF_Order_Cancel
ALTER TABLE Order ADD DEFAULT 0 FOR Cancel