SQL 更改列默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25812331/
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
Alter column default value
提问by SZT
I know you can change the default value of an existing column like this:
我知道您可以像这样更改现有列的默认值：
ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N'SANDNES' FOR CityBorn;
But according to thismy query supposed to work:
但根据this我的查询应该工作：
ALTER TABLE MyTable ALTER COLUMN CreateDate DATETIME NOT NULL
CONSTRAINT DF_Constraint DEFAULT GetDate()
So here I'm trying to make my column Not Null and also set the Default value. But getting Incoorect Syntax Error near CONSTRAINT. Am I missing sth?
所以在这里我试图让我的列不为空并设置默认值。但是在 CONSTRAINT 附近遇到 Incoorect 语法错误。我错过了什么吗？
采纳答案by Deepshikha
I think issue here is with the confusion between Create Tableand Alter Tablecommands.
If we look at Create tablethen we can add a default value and default constraint at same time as:
我认为这里的问题在于Create Table和Alter Table命令之间的混淆。如果我们看一下，Create table那么我们可以同时添加一个默认值和默认约束：
<column_definition> ::=
column_name <data_type>
[ FILESTREAM ]
[ COLLATE collation_name ]
[ SPARSE ]
[ NULL | NOT NULL ]
[
[ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
| [ IDENTITY [ ( seed,increment ) ] [ NOT FOR REPLICATION ]
]
[ ROWGUIDCOL ]
[ <column_constraint> [ ...n ] ]
[ <column_index> ]
ex:
CREATE TABLE dbo.Employee
(
CreateDate datetime NOT NULL
CONSTRAINT DF_Constraint DEFAULT (getdate())
)
ON PRIMARY;
you can check for complete definition here: http://msdn.microsoft.com/en-IN/library/ms174979.aspx
您可以在此处查看完整定义：http: //msdn.microsoft.com/en-IN/library/ms174979.aspx
but if we look at the Alter Tabledefinition then with ALTER TABLE ALTER COLUMNyou cannot add
CONSTRAINTthe options available for ADDare:
但是，如果我们查看Alter Table定义，那么ALTER TABLE ALTER COLUMN您无法添加
CONSTRAINT以下可用选项ADD：
| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]
Check here: http://msdn.microsoft.com/en-in/library/ms190273.aspx
在这里查看：http: //msdn.microsoft.com/en-in/library/ms190273.aspx
So you will have to write two different statements one for Altering column as:
因此，您必须为 Altering 列编写两个不同的语句：
ALTER TABLE MyTable ALTER COLUMN CreateDate DATETIME NOT NULL;
and another for altering table and add a default constraint
另一个用于更改表并添加默认约束
ALTER TABLE MyTable ADD CONSTRAINT DF_Constraint DEFAULT GetDate() FOR CreateDate;
ALTER TABLE MyTable ADD CONSTRAINT DF_Constraint DEFAULT GetDate() FOR CreateDate;
Hope this helps!!!
希望这可以帮助！！！
回答by Plamen Kasabov
There is no direct way to change default value of a column in SQL server, but the following parameterized script will do the work:
没有直接的方法可以更改 SQL Server 中列的默认值，但以下参数化脚本将完成这项工作：
DECLARE @table nvarchar(100)
DECLARE @column nvarchar(100)
DECLARE @newDefault nvarchar(100)
SET @table='TableName'
SET @column='ColumnName'
SET @newDefault='0'
IF EXISTS (select name from sys.default_constraints
where parent_object_id = object_id(@table)
and parent_column_id = columnproperty(object_id(@table), @column, 'ColumnId'))
BEGIN
DECLARE @constraintName as nvarchar(200)
DECLARE @constraintQuery as nvarchar(2000)
SELECT @constraintName = name from sys.default_constraints
where parent_object_id = object_id(@table) and parent_column_id = columnproperty(object_id(@table),@column, 'ColumnId')
SET @constraintQuery = 'ALTER TABLE '+@table+' DROP CONSTRAINT '+@constraintName +'; ALTER TABLE '+ @table
+ ' ADD CONSTRAINT ' + @constraintName +' DEFAULT '+@newDefault+' FOR '+@column
EXECUTE sp_executesql @constraintQuery
END
Just fill the parameters and execute. The script removes existing constraint and creates a new one with designated default value.
只需填写参数并执行即可。该脚本删除现有约束并创建一个具有指定默认值的新约束。
回答by Alex K.
For altering an existing column you need to:
要更改现有列，您需要：
ALTER TABLE MyTable ALTER COLUMN CreateDate DATETIME NOT NULL;
ALTER TABLE MyTable ADD CONSTRAINT DF_Constraint DEFAULT GetDate() FOR CreateDate;
回答by Krishnraj Rana
It should be -
它应该是 -
ALTER TABLE MyTable
ALTER COLUMN CreateDate DATETIME NOT NULL;
ALTER TABLE MyTable
ADD CONSTRAINT DF_Constraint DEFAULT GetDate() FOR CreateDate;
回答by Will Marcouiller
From MSDN ALTER TABLEexamples:
来自 MSDN ALTER TABLE示例：
D. Adding a DEFAULT constraint to an existing column
D. 向现有列添加 DEFAULT 约束
The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. A DEFAULTconstraint is then added to the second column. To verify that the default is applied, another value is inserted into the first column, and the table is queried.
下面的示例创建一个包含两列的表，并在第一列中插入一个值，另一列保持NULL。甲DEFAULT然后约束被添加到第二列中。为了验证是否应用了默认值，将另一个值插入到第一列中，并查询该表。
CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO
So in short, you need the ADD CONSTRAINT
简而言之，您需要 ADD CONSTRAINT
ALTER TABLE MyTable
ALTER COLUMN CreateDate DATETIME NOT NULL ;
ALTER TABLE MyTable
ADD CONSTRAINT DF_Constraint DEFAULT GetDate() FOR CreateDate;
