向现有表插入非空列。

I have tried:

我有尝试:

ALTER TABLE MY_TABLE 
ADD STAGE INT NOT NULL;

But it gives this error message:

但是它给出了这个错误信息:

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified

ALTER TABLE只允许添加可以包含nulls的列，或者有指定的默认定义。

6 个解决方案

#1

169

As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint:

作为一个选项，您可以首先创建可空列，然后用有效的not null值更新您的表列，最后更改列来设置not null约束:

ALTER TABLE MY_TABLE ADD STAGE INT NULL
GO
UPDATE MY_TABLE SET <a valid not null values for your column>
GO
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL
GO

Another option is to specify correct default value for your column:

另一个选项是为您的列指定正确的默认值:

ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL DEFAULT '0'

UPD: Please note that answer above contains GO which is a must when you run this code on Microsoft SQL server. If you want to perform the same operation on Oracle or MySQL you need to use semicolon ; like that:

UPD:请注意，在Microsoft SQL server上运行此代码时，上面的答案包含GO。如果您想在Oracle或MySQL上执行相同的操作，则需要使用分号;像这样:

ALTER TABLE MY_TABLE ADD STAGE INT NULL;
UPDATE MY_TABLE SET <a valid not null values for your column>;
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL;

#2

If you aren't allowing the column to be Null you need to provide a default to populate existing rows. e.g.

如果您不允许该列为空，那么您需要提供一个默认值来填充现有的行。如。

ALTER TABLE dbo.YourTbl ADD
    newcol int NOT NULL CONSTRAINT DF_YourTbl_newcol DEFAULT 0

On Enterprise Edition this is a metadata only change since 2012

在企业版上，这是自2012年以来的元数据。

#3

The error message is quite descriptive, try:

错误消息非常具有描述性，请尝试:

ALTER TABLE MyTable ADD Stage INT NOT NULL DEFAULT '-';

#4

Other SQL implementations have similar restrictions. The reason is that adding a column requires adding values for that column (logically, even if not physically), which default to NULL. If you don't allow NULL, and don't have a default, what is the value going to be?

其他SQL实现也有类似的限制。原因是添加一个列需要为该列添加值(逻辑上，即使不是物理上的)，默认为NULL。如果不允许NULL，并且没有默认值，那么它的值是多少?

Since SQL Server supports ADD CONSTRAINT, I'd recommend Pavel's approach of creating a nullable column, and then adding a NOT NULL constraint after you've filled it with non-NULL values.

由于SQL Server支持添加约束，所以我建议Pavel创建一个可空列的方法，然后在填充了非空值之后添加NOT NULL约束。

#5

This worked for me, can also be "borrowed" from the design view, make changes -> right click -> generate change script.

这对我来说很有效，也可以从设计视图中“借用”，做出更改——>右击——>生成更改脚本。

BEGIN TRANSACTION
GO
ALTER TABLE dbo.YOURTABLE ADD
    YOURCOLUMN bit NOT NULL CONSTRAINT DF_YOURTABLE_YOURCOLUMN DEFAULT 0
GO
COMMIT

#6

ALTER TABLE `MY_TABLE` ADD COLUMN `STAGE` INTEGER UNSIGNED NOT NULL AFTER `PREV_COLUMN`;

#1

169