在现有表中插入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屋!