修改 SQL Server 中的默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15547210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Modify Default value in SQL Server
提问by Ruben.Canton
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.
我正在尝试使用 SQL Server 2008 中的 SQL 语句更改列的默认值。我在很多地方发现如何在创建表/添加列时设置默认值，但没有找到如何设置它/一旦该列已经存在，就修改它。
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?
有没有一种方法可以用一个简单的句子做我想做的事？
Thanks.
谢谢。
回答by Adam Luniewski
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 _tempresult:
sp_help _temp结果：
constraint_type constraint_name
DEFAULT on column x DF___temp__x__5A3B20F9
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:
所以列定义中的 default 子句只是创建约束的快捷方式；默认值不是列的固有属性。如果要修改现有列的默认值，则必须先删除约束：
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
回答by user355308
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
回答by Young Bob
You should drop the DEFAULT constraint and add a new one like this
您应该删除 DEFAULT 约束并添加一个像这样的新约束
alter table Mytable
drop constraint <constraint name>
go
alter table MyTable
add constraint df_MyTable_MyColumn default -1 for MyColumn
go
Use sp_helpconstraint MyTable to get constraint names
使用 sp_helpconstraint MyTable 获取约束名称
回答by JodyT
DEFAULTis a constraint. You want to use ALTER TABLE ADD CONSTRAINT.
DEFAULT是一个约束。您想使用ALTER TABLE ADD CONSTRAINT.
ALTER TABLE MyTable
ADD CONSTRAINT <constraint name>
DEFAULT -1 FOR MyColumn
You'll need to drop the current constraint first, like:
您需要先删除当前约束，例如：
ALTER TABLE MyTable
DROP CONSTRAINT <constraint name>
回答by Igor
Add default value for column if it is not exists
如果不存在，则为列添加默认值
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
回答by MrRimmer
I've had to change the default constraint on many fields in existing databases so wanted do do it in one hit. Hope this helps...
我不得不更改现有数据库中许多字段的默认约束，所以想一次性完成。希望这可以帮助...
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
Obviously you'll need to amend the query and copy the output to another tab to be executed.
显然，您需要修改查询并将输出复制到另一个要执行的选项卡。
