How to change default column value in sql server
In my table, when changed, I gave the default value "1". Now I need to reset the value to the default "0". I tried the following script but it throws an error.
ALTER TABLE Order ADD Cancel BIT CONSTRAINT [DF_Order_Cancel] DEFAULT ((1)) NOT NULL;
Here I need to reset the default value "0" instead of "1".
I tried the script below but it was still giving error.
ALTER TABLE Order ADD DEFAULT (0) FOR Cancel
+3
source to share
2 answers
Remove the constraint first.
alter table Order drop constraint DF_Order_Cancel
Then re-create it.
ALTER TABLE Order ADD DEFAULT 0 FOR Cancel
Edit: The following statements work 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
+5
source to share
You need to get the name of the constraint. Run the following code and you can see the constraint name in the results.
sp_helpconstraint tableName
Once you've got the constraint name, you can use this code to change the default for your column:
alter table tableName
drop constraint constraintName
go
alter table tableName
add constraint df_tableName_columnName default 0 for columnName
go
0
source to share