如何更改sql server中的默认列值

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.

在我修改的表格中，我给默认设置了“1”。现在我需要重置默认值“0”。我尝试了下面的脚本，但是它抛出了一个错误。

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'.

这里我需要将默认值重置为'0'而不是'1'。

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

我尝试了下面的脚本，但它仍然抛出一个错误。

ALTER TABLE Order ADD DEFAULT (0) FOR Cancel

2 个解决方案

#1

First, delete the constraint.

首先,删除约束。

alter table Order drop constraint DF_Order_Cancel

Then recreate it.

然后重新创建它。

ALTER TABLE Order ADD DEFAULT 0 FOR Cancel

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

#2

You need to get the constraint name. Execute the code bellow and in the results you can see the constraint name.

您需要获得约束名称。执行下面的代码，在结果中可以看到约束名。

sp_helpconstraint tableName

After you got the constraint name then you can use this code to change the default value for your column :

获得约束名称后，可以使用此代码更改列的默认值:

alter table tableName
drop constraint constraintName
go
alter table tableName
add constraint df_tableName_columnName default 0 for columnName
go

#1