SQL 我可以添加一个没有 DEFAULT 值的非空列吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3997966/
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
Can I add a not null column without DEFAULT value
提问by Saubhagya
Can I add a column which is I specify as NOT NULL,I don't want to specify the DEFAULT value but MS-SQL 2005 says:
我可以添加一个我指定为 NOT NULL 的列，我不想指定 DEFAULT 值，但 MS-SQL 2005 说：
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'test' cannot be added to non-empty table 'shiplist' because it does not satisfy these conditions.
ALTER TABLE 只允许添加可以包含空值的列，或者指定了 DEFAULT 定义，或者添加的列是标识或时间戳列，或者如果前面的条件都不满足，则表必须为空以允许添加本专栏的。列 'test' 不能添加到非空表 'shiplist' 中，因为它不满足这些条件。
If YES, please let me know the syntax, if No please specify the reason.
如果是，请告诉我语法，如果不是，请说明原因。
回答by Gimly
No, you can't.
不，你不能。
Because if you could, SQL wouldn't know what to put as value in the already existing records. If you didn't have any records in the table it would work without issues.
因为如果可以，SQL 将不知道将什么作为值放入现有记录中。如果表中没有任何记录，它就可以正常工作。
The simplest way to do this is create the column with a default and then remove the default.
最简单的方法是使用默认值创建列，然后删除默认值。
ALTER TABLE dbo.MyTable ADD
MyColumn text NOT NULL CONSTRAINT DF_MyTable_MyColumn DEFAULT 'defaultValue'
ALTER TABLE dbo.MyTable
DROP CONSTRAINT DF_MyTable_MyColumn
Another alternative would be to add the column without the constraint, fill the values for all cells and add the constraint.
另一种选择是添加没有约束的列，填充所有单元格的值并添加约束。
回答by Paul Tomblin
Add the column to the table, update the existing rows so none of them are null, and then add a "not null" constraint.
将列添加到表中，更新现有行，使它们都不为空，然后添加“非空”约束。
回答by Damien_The_Unbeliever
No - SQL Server quite reasonably rejects this, because it wouldn't know what value existing rows should have
否 - SQL Server 相当合理地拒绝了这一点，因为它不知道现有行应该具有什么值
It's easy to create a DEFAULT at the same time, and then immediately drop it.
很容易同时创建一个 DEFAULT，然后立即删除它。
回答by sad_robot
I use this approach to insert NOT NULL column without default value
我使用这种方法插入没有默认值的 NOT NULL 列
ALTER TABLE [Table] ADD [Column] INT NULL
GO
UPDATE [Table] SET [Column] = <default_value>
ALTER TABLE [Table] ALTER COLUMN [Column] INT NOT NULL
回答by chris
No you cannot. But you can consider to specify the default value to ('')
你不能。但是可以考虑将默认值指定为('')
回答by abatishchev
No.
不。
Just use empty string ''(in case of character type) or 0 (if numeric), etc as DEFAULT value
只需使用空字符串''（如果是字符类型）或 0（如果是数字）等作为默认值
回答by Will Marcouiller
No, you can't, as SQL Server, or any other database engines will force this new column to be null for existing rows into your data table. But since you do not allow a NULL, you are required to provide a default value in order to respect your own constraint. This falls under great sense! The DBE will not extrapolate a value for non-null values for the existing rows.
不，您不能，因为 SQL Server 或任何其他数据库引擎将强制此新列对于数据表中的现有行为空。但是由于您不允许 NULL，您需要提供一个默认值以尊重您自己的约束。这很有道理！DBE 不会为现有行的非空值推断值。
回答by Gennady Vanin Геннадий Ванин
@Damien_The_Unbeliever's comment, Is it adding computed column? Neither question nor answer implied anything like that. In case of computed column the error states:
@ Damien_The_Unbeliever 的评论，是不是增加了计算列？问题和回答都没有暗示任何类似的东西。在计算列的情况下，错误状态：
"Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted"
“只能在计算列上创建 UNIQUE 或 PRIMARY KEY 约束，而 CHECK、FOREIGN KEY 和 NOT NULL 约束要求计算列被持久化”
OK, if to continue this guessing game, here is my script illustrating the adding of "NOT NULL" column in one "ALTER TABLE" step:
好的，如果要继续这个猜谜游戏，这里是我的脚本，说明在一个“ALTER TABLE”步骤中添加“NOT NULL”列：
CREATE TABLE TestInsertComputedColumn
(
FirstName VARCHAR(100),
LastName CHAR(50)
);
insert into TestInsertComputedColumn(FirstName,LastName)
select 'v', 'gv8';
select * from TestInsertComputedColumn;
ALTER TABLE TestInsertComputedColumn
ADD FullName As FirstName + LastName PERSISTED NOT NULL;
select * from TestInsertComputedColumn;
--drop TABLE TestInsertComputedColumn;
