SQL Server中修改默认值

Question

我正在尝试在SQL Server 2008中使用SQL语句更改列的默认值。我在许多地方找到了如何在创建表/添加列时设置默认值的方法，但没有找到如何在该列已经存在时设置/修改它的方法。

这是我可以用来设置添加时的语句：

ALTER TABLE MyTable ADD MyColumn int NOT NULL DEFAULT 0

这样做是有效的，但如果我稍后尝试进行修改：

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

这些都没有语法上的正确性，而且我找不到任何可以实现我所期望的语法。我唯一能想到的选择是添加一个新列，复制前一列中的值，然后删除前一列和新列以进行更改，但这对我来说似乎不太合适。

是否有一种简单的方法可以只用一个句子实现我想要的效果？

谢谢。

- Ruben.Canton

Answer 1

当您添加具有默认值的列时，所发生的是创建了一个默认约束：

create table _temp 
( x int default 1)

sp_help _temp 的结果：

constraint_type constraint_name
DEFAULT on column x DF___temp__x__5A3B20F9

因此，列定义中的默认子句只是创建约束的快捷方式；默认值不是列的固有属性。如果您想要修改已存在列的默认值，您必须先删除约束：

alter table _temp drop constraint DF___temp__x__5A3B20F9 

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

alter table _temp add constraint DF_temp_x default 2 for x

- AdamL

---

我只想补充一下如何获取约束名到你的解释中： SELECT NAME FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID('dbo.MyTable'); 这样做有助于你知道要删除/添加哪个约束。 - Ruben.Canton

这清楚地说明了为什么“明确命名”约束条件是如此重要！想象一下，如果你想删除这个约束条件……如果你知道它叫做DF_Temp_X（而不是系统生成的DF___temp__x__5A3B20F9），那么这将变得更加容易。 - marc_s

"sp_help TABLE_NAME" 也可以做到这一点。 - AdamL

执行 sp_helpconstraint @objname = 'TableName' - boubkhaled

Answer 2

DECLARE @Command nvarchar(max), @ConstraintName nvarchar(max), @TableName nvarchar(max), @ColumnName nvarchar(max)
SET @TableName = 'TableName'
SET @ColumnName = 'ColumnName'
SELECT @ConstraintName = name
    FROM sys.default_constraints
    WHERE parent_object_id = object_id(@TableName)
        AND parent_column_id = columnproperty(object_id(@TableName), @ColumnName, 'ColumnId')

SELECT @Command = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName  
EXECUTE sp_executeSQL @Command

SELECT @Command = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @ConstraintName + ' DEFAULT 1 FOR ' + @ColumnName 
EXECUTE sp_executeSQL @Command

- user355308

---

可能是作为对 SQL Server 需要大量代码才能删除由系统生成的约束名称的默认值的反应，与例如 PostgreSQL 相比：ALTER TABLE TableName ALTER COLUMN ColumnName DROP DEFAULT; - MrBackend

Answer 3

您应该删除DEFAULT约束并添加一个新的约束，如下所示：

alter table Mytable
drop constraint <constraint name>
go
alter table MyTable
add constraint df_MyTable_MyColumn default -1 for MyColumn
go

使用 sp_helpconstraint MyTable 命令获取约束名

- Young Bob

---

我明白了，其他页面也在谈论这个约束条件，但是我没有仔细阅读，看着这些语句，我以为它回答的是另外一个问题。感谢澄清。 - Ruben.Canton

Answer 4

DEFAULT 是一种约束条件。你需要使用 ALTER TABLE ADD CONSTRAINT 命令。

ALTER TABLE MyTable
ADD CONSTRAINT <constraint name>
DEFAULT -1 FOR MyColumn

您需要首先删除当前的约束，例如:

ALTER TABLE MyTable
DROP CONSTRAINT <constraint name>

- JodyT

Answer 5

如果列不存在，则为其添加默认值

DECLARE @defaultLock VARCHAR(100)
SELECT @defaultLock = (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name = 'Lock' AND object_id = object_id('dbo.Positions'))
    IF @defaultLock IS NULL
        ALTER TABLE Positions ADD DEFAULT (0) FOR Lock

- Igor

Answer 6

我不得不在现有数据库中更改许多字段的默认约束，因此希望一次性完成。希望这可以帮到你...

select 'USE ' + DB_NAME()
SELECT 'ALTER TABLE '+ src3.name + '.' + src1.name +' drop constraint '+ src.name + '
ALTER TABLE ' + src3.name + '.' +  src1.name +' ADD CONSTRAINT '+ src.name + ' DEFAULT getutcdate() FOR ' + src2.name -- amend default constrint accordingly.
    FROM sys.tables src1
      inner join sys.schemas src3
        on src3.schema_id = src1.schema_id
      inner join sys.columns src2
        on src2.object_id = src1.object_id
      inner join sys.default_constraints src
        on src.parent_column_id = src2.column_id
           and src.parent_object_id = src1.object_id
    WHERE  (-- pick up existing constraint definition
            src.definition like '%getdate%'
             or src.definition like '%current\_timestamp%' escape '\'
            )
            and src1.type = 'U'
order by  src3.name + '.' + src1.name

显然，您需要修改查询并将输出复制到另一个选项卡中以执行。

- MrRimmer