我可以添加一个没有 DEFAULT 值的非空列吗 [英] Can I add a not null column without DEFAULT value

问题描述

我可以添加一个我指定为 NOT NULL 的列吗，我不想指定 DEFAULT 值但 MS-SQL 2005 说:

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:

ALTER TABLE 只允许添加可以包含空值的列，或者指定了 DEFAULT 定义的列，或者添加的列是标识或时间戳列，或者如果前面的条件都不满足，则表必须为空以允许添加此列.列 'test' 不能添加到非空表 'shiplist' 中，因为它不满足这些条件.

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.

如果是，请告诉我语法，如果不是，请说明原因.

If YES, please let me know the syntax, if No please specify the reason.

推荐答案

不，你不能.

因为如果可以，SQL 将不知道将什么作为值放入现有记录中.如果表中没有任何记录，它就可以正常工作.

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.

最简单的方法是使用默认值创建列，然后删除默认值.

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.

这篇关于我可以添加一个没有 DEFAULT 值的非空列吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！