向 SQL Server 中的现有表添加具有默认值的列 [英] Add a column with a default value to an existing table in SQL Server
问题描述
如何将具有默认值的列添加到 SQL Server 2000/SQL Server 2005?
How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?
推荐答案
语法:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
示例:
ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
注意事项:
可选约束名称:
如果您省略 CONSTRAINT D_SomeTable_SomeCol
,则 SQL Server 将自动生成
具有有趣名称的默认约束,例如:DF__SomeTa__SomeC__4FB7FEF6
Notes:
Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol
then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6
可选的 With-Values 声明:WITH VALUES
仅在您的列为 Nullable 时才需要
并且您想要用于现有记录的默认值.
如果您的列是 NOT NULL
,那么它将自动使用默认值
对于所有现有记录,无论您是否指定 WITH VALUES
.
Optional With-Values Statement:
The WITH VALUES
is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL
, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES
or not.
插入如何与默认约束一起工作:
如果你在SomeTable
中插入一个Record并且不指定SomeCol
的值,那么它会默认为<代码>0代码>.
如果您插入记录 和 将 SomeCol
的值指定为 NULL
(并且您的列允许空值),
那么默认约束将不被使用,NULL
将被插入作为值.
How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable
and do not Specify SomeCol
's value, then it will Default to 0
.
If you insert a Record and Specify SomeCol
's value as NULL
(and your column allows nulls),
then the Default-Constraint will not be used and NULL
will be inserted as the Value.
注释是基于每个人在下面的很好的反馈.
特别鸣谢:
@Yatrix、@WalterStabosz、@YahooSerious 和 @StackMan 发表评论.
Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.
这篇关于向 SQL Server 中的现有表添加具有默认值的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!