向 SQL Server 中的现有表添加具有默认值的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/92082/
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
Add a column with a default value to an existing table in SQL Server
提问by Mathias
How can I add a column with a default value to an existing table in SQL Server 2000/ SQL Server 2005?
如何向SQL Server 2000/ SQL Server 2005 中的现有表添加具有默认值的列？
回答by James Boother
Syntax:
句法：
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Example:
例子：
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.
Notes:
笔记：
Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeColthen SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6
可选的约束名称：
如果您省略，CONSTRAINT D_SomeTable_SomeCol那么 SQL Server 将自动生成
一个带有有趣名称的默认约束，例如：DF__SomeTa__SomeC__4FB7FEF6
Optional With-Values Statement:
The WITH VALUESis 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 VALUESor not.
可选的值声明：
本WITH VALUES当你的列可以为空时，才需要
和想要的价值用于现有记录的默认值。
如果您的 Column 是NOT NULL，那么
无论您是否指定，它都会自动为所有现有记录使用默认值WITH VALUES。
How Inserts work with a Default-Constraint:
If you insert a Record into SomeTableand do notSpecify SomeCol's value, then it will Default to 0.
If you insert a Record andSpecify SomeCol's value as NULL(and your column allows nulls),
then the Default-Constraint will notbe used and NULLwill be inserted as the Value.
插入如何与默认约束一起工作：
如果您插入一个 RecordSomeTable并且没有指定SomeCol的值，那么它将默认为0。
如果您插入 Record并将其SomeCol值指定为NULL（并且您的列允许空值），
则不会使用Default-Constraint 并将NULL作为值插入。
Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.
笔记是基于下面每个人的很好的反馈。
特别感谢：
@Yatrix、@WalterStabosz、@YahooSerious 和@StackMan 的评论。
回答by dbugger
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO
The inclusion of the DEFAULTfills the column in existingrows with the default value, so the NOT NULL constraint is not violated.
包含DEFAULT 会使用默认值填充现有行中的列，因此不会违反 NOT NULL 约束。
回答by phunk_munkie
When adding a nullable column, WITH VALUESwill ensure that the specific DEFAULT value is applied to existing rows:
添加可为空的列时，WITH VALUES将确保将特定的 DEFAULT 值应用于现有行：
ALTER TABLE table
ADD column BIT -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
回答by ddc0660
ALTER TABLE <table name>
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
回答by Evan V
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
回答by jalbert
Beware when the column you are adding has a NOT NULLconstraint, yet does not have a DEFAULTconstraint (value). The ALTER TABLEstatement will fail in that case if the table has any rows in it. The solution is to either remove the NOT NULLconstraint from the new column, or provide a DEFAULTconstraint for it.
当您添加的列有NOT NULL约束但没有DEFAULT约束（值）时要小心。ALTER TABLE在这种情况下，如果表中有任何行，则该语句将失败。解决方案是NOT NULL从新列中删除约束，或为其提供DEFAULT约束。
回答by adeel41
The most basic version with two lines only
最基本的版本只有两行
ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
回答by JerryOL
Use:
用：
-- Add a column with a default DateTime
-- to capture when each record is added.
ALTER TABLE myTableName
ADD RecordAddedDate SMALLDATETIME NULL DEFAULT (GETDATE())
GO
回答by Gabriel L.
If you want to add multiple columns you can do it this way for example:
如果你想添加多列，你可以这样做，例如：
ALTER TABLE YourTable
ADD Column1 INT NOT NULL DEFAULT 0,
Column2 INT NOT NULL DEFAULT 1,
Column3 VARCHAR(50) DEFAULT 'Hello'
GO
回答by giá vàng
Use:
用：
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Reference: ALTER TABLE (Transact-SQL)(MSDN)
参考：ALTER TABLE (Transact-SQL)(MSDN)
