更改列默认值 [英] Alter column default value

查看:145
本文介绍了更改列默认值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我知道您可以更改现有列的默认值,例如

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 TableAlter 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆