SQL 如何为已经存在的列添加默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3058072/
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
How to add a default value to an already existing column?
提问by Earlz
I have an existing column in my SQL Server database. I have tried about everything I can think of but can not get a default value to be added to the column. What works in every other database is
我的 SQL Server 数据库中有一个现有列。我已经尝试了所有我能想到的但无法获得要添加到列中的默认值。在其他所有数据库中都有效的是
alter table mytable
alter column mycolumn set default(now()) --mycolumn is a datetime
How do I do this in SQL Server?
如何在 SQL Server 中执行此操作？
The error I get for that exact syntax is incorrect syntax near the keyword 'set'
我得到的那个确切语法的错误是 incorrect syntax near the keyword 'set'
回答by OMG Ponies
Use:
用：
ALTER TABLE dbo.mytable
ADD CONSTRAINT def_mycolumn DEFAULT GETDATE() FOR mycolumn
For more info, see: Working with Default Constraints
有关更多信息，请参阅：使用默认约束
回答by suryakiran
If you want to change the default value of an already existing column. You need to first drop the constraint and then add the constraint all over again as below
如果要更改现有列的默认值。您需要先删除约束，然后重新添加约束，如下所示
ALTER TABLE <TABLE>
DROP CONSTRAINT <CONSTRAINT NAME>
ALTER TABLE <TABLE>
ADD CONSTRAINT <CONSTRAINT NAME> DEFAULT <VALUE> for <COLUMN>
If you are not having the Constraint details of the table, you can use the below query
如果您没有表的约束详细信息，则可以使用以下查询
sp_helpconstraint <TABLE>
