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'
我得到的错误是在关键字" set "附近的错误语法
2 个解决方案
#1
65
Use:
使用:
ALTER TABLE dbo.mytable
ADD CONSTRAINT def_mycolumn DEFAULT GETDATE() FOR mycolumn
For more info, see: Working with Default Constraints
有关更多信息，请参见:使用默认约束
#2
6
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>
#1
65
Use:
使用:
ALTER TABLE dbo.mytable
ADD CONSTRAINT def_mycolumn DEFAULT GETDATE() FOR mycolumn
For more info, see: Working with Default Constraints
有关更多信息，请参见:使用默认约束
#2
6
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>