更改列默认值 [英] Alter column default value
问题描述
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 this my query supposed to work:
ALTER TABLE MyTable ALTER COLUMN CreateDate DATETIME NOT NULL
CONSTRAINT DF_Constraint DEFAULT GetDate()
所以在这里,我试图将我的列设置为非空,并设置默认值.但是在CONSTRAINT附近出现了不正确的语法错误.我想念什么吗?
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?
推荐答案
我认为这里的问题是Create Table
和Alter Table
命令之间的混淆.
如果我们查看Create table
,则可以同时添加一个默认值和默认约束:
I think issue here is with the confusion between Create Table
and Alter Table
commands.
If we look at Create table
then we can add a default value and default constraint at same time as:
<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;
您可以在此处检查完整的定义: http://msdn.microsoft.com/en-IN/library/ms174979.aspx
you can check for complete definition here: http://msdn.microsoft.com/en-IN/library/ms174979.aspx
,但是如果我们查看Alter Table
定义,则使用ALTER TABLE ALTER COLUMN
时,您无法添加
CONSTRAINT
ADD
可用的选项是:
but if we look at the Alter Table
definition then with ALTER TABLE ALTER COLUMN
you cannot add
CONSTRAINT
the options available for ADD
are:
| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]
在此处检查: http://msdn.microsoft.com/zh-CN/library/ms190273.aspx
因此您将不得不为Altering列编写两个不同的语句,如下所示:
So you will have to write two different statements one for Altering column as:
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;
希望这会有所帮助!
这篇关于更改列默认值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!