在现有表中插入NOT NULL列 [英] insert a NOT NULL column to an existing table

问题描述

我尝试过:

ALTER TABLE MY_TABLE 
ADD STAGE INT NOT NULL;

但是它给出了此错误消息:

But it gives this error message:

ALTER TABLE仅允许添加可以包含空值或 指定了默认定义

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

推荐答案

作为一个选项，您可以首先创建Null-able列，然后使用有效的not null值更新表列，最后使用ALTER列设置NOT NULL约束:

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:

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:请注意，上面的答案包含GO，这是在Microsoft SQL Server上运行此代码时必须的.如果要在Oracle或MySQL上执行相同的操作，则需要这样使用分号;:

---

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:

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;

这篇关于在现有表中插入NOT NULL列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！