修改 SQL Server 中的默认值 [英] Modify Default value in SQL Server

问题描述

我正在尝试使用 SQL Server 2008 中的 SQL 语句更改列的默认值.我在很多地方找到了如何在创建表/添加列时设置默认值，但没有找到如何设置默认值一旦该列已经存在，就对其进行设置/修改.

I'm trying to change the default value of a column using a SQL statement in SQL Server 2008. I've found in many places how to set the default value when you create a table/add a column but not how to set it/modify it once the column already exists.

这是我可以用来设置添加的内容:

This is what I can use to set it on adding:

ALTER TABLE MyTable ADD MyColumn int NOT NULL DEFAULT 0

这行得通，但如果我稍后尝试修改它:

And that works, but if I try to modify it later:

ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL DEFAULT -1
ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL SET DEFAULT -1

这些在语法上都不正确，而且我找不到可以在任何地方做我假装的语法.我带来的唯一选择是添加一个新列，从上一列复制值，然后删除上一列和新列以进行更改，但这对我来说似乎不正确.

None of those are syntactically correct, and I don't find the syntax to do what I pretend anywhere. The only option I come with is to add a new column, copy values from previous column, then remove previous column and new column to make the change, but that doesn't seem right to me.

有没有一种方法可以用简单的一句话来做我想做的事?

Is there a way of doing what I want in just one simple sentence?

谢谢.

推荐答案

当您添加具有默认值的列时，会发生的情况是创建了一个默认约束:

When you add a column with default value, what happens is that there's a default constraint being created:

create table _temp 
( x int default 1)

sp_help _temp 结果:

constraint_type constraint_name
DEFAULT on column x DF___temp__x__5A3B20F9

所以列定义中的 default 子句只是创建约束的快捷方式；默认值不是列的固有属性.如果要修改现有列的默认值，则必须先删除约束:

So default clause in column definition is just a shortcut for creating a constraint; default value is not an intrinsic property of a column. If you want to modify default for already existing column, you have to drop the constraint first:

alter table _temp drop constraint DF___temp__x__5A3B20F9 

然后创建一个新的默认约束:

And then create a new default constraint:

alter table _temp add constraint DF_temp_x default 2 for x

这篇关于修改 SQL Server 中的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！