向现有表中插入一个非空列

Question

我已经尝试过：

ALTER TABLE MY_TABLE 
ADD STAGE INT NOT NULL;

但它会给出这个错误信息：

ALTER TABLE只允许添加能够包含null值或已指定DEFAULT定义的列

- ANP

---

针对PostgreSQL：https://dev59.com/questions/lnRB5IYBdhLWcg3wyqEd - M Imam Pratama

Answer 1

作为一种选择，您可以最初创建可空列，然后使用有效的非空值更新表列，最后使用 ALTER column 来设置 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

另一种选择是为您的列指定正确的默认值：

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

---

更新：请注意上面的答案包含 GO，在Microsoft SQL Server上运行此代码时必须使用它。如果您想在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;

- Pavel Morshenyuk

---

如果您有手动输入字段值的选项，我个人更喜欢第一种方式。这样，您就不必担心在不需要默认约束的地方创建和删除它。 - Mark W Dickson

@MarkWDickson - 对我来说，第一个似乎更危险。如果错误的列滑入了设置语句会发生什么：UPDATE MY_TABLE SET Employee_Salary = 0 - acarlon

使用SQLite的ANDROID开发人员请注意，SQLite不支持“ALTER COLUMN”。 - Sdghasemi

我以前从未见过 GO，并且它似乎不是 SQL 规范的一部分，因此对于未被支持的工具执行的脚本可能会导致失败。只使用分号？我不建议传播微软标准，因为他们很少关心任何已经建立和合理的标准，而是发明自己的标准。除此之外，这是一个有帮助的答案。 - Neonit

感谢 @Neon 的评论，但原问题是关于 MS SQL Server 的，这就是为什么会有 GO 关键字的原因。不过我会在我的回答中加上一个注释。 - Pavel Morshenyuk

Answer 2

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

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

自2012年起，在企业版中，这是一个仅涉及元数据的更改，详情请参考此链接

- Martin Smith

---

你不必须这样做，但这是一个选项。 - Matt

@Matt 是的，你需要这样做。如果表中有任何行，并且给定了条件“如果您不允许列为空”，那么新列如何避免违反现有行的NOT NULL约束呢？ - Martin Smith

另一个选项是将列添加为可空，然后使用更新语句更新表中的每一行，然后将列更改为非空。这样，您就不会留下默认约束，而这可能并不是您想要的。 - Matt

@Matt - 这可能会慢很多，特别是在使用默认值的方法作为仅元数据更改（即在企业版或Azure SQL数据库上具有运行时常量值的默认值）的情况下。如果不希望继续使用默认值，则很容易删除默认值。首先，UPDATE需要更新所有行，然后将其更改为NOT NULL也会（有些令人惊讶地）增加日志文件的大小。https://dba.stackexchange.com/questions/29522/why-does-alter-column-to-not-null-cause-massive-log-file-growth - Martin Smith

实际上，我刚意识到这是对先前评论的一年延迟回复。在这种情况下，您确实允许该列为NULL - 尽管仅是暂时的。如果您想运行ALTER TABLE ADD column NOT NULL并且表中有行，则需要提供默认值。 - Martin Smith

这就像说1+1和1+2-1是一样的。当然，它们都能带你到目的地。 - Bpainter

Answer 3

更快的解决方案

如果您需要在包含大量数据的表上执行此操作，使用ADD-UPDATE-ALTER选项非常缓慢（对于数百万行数据可能需要数小时）。

如果您不希望在表上设置默认值，以下是创建列并删除默认约束的完整代码（即使对于大型表也几乎瞬间完成）：

ALTER TABLE my_table ADD column_name INT NOT NULL CONSTRAINT my_table_default_constraint DEFAULT 0
GO

ALTER TABLE my_table DROP CONSTRAINT my_table_default_constraint 
GO

这是针对 SQL Server 的内容

- Johan

---

救命稻草，非常感谢。 - ConnieMnemonic

Answer 4

错误信息非常详细，请尝试：

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

- a'r

---

以编程为相关内容，将以下内容从英语翻译成中文。仅返回翻译文本：在默认部分中，“减号”的意义是什么？ - Mladen B.

所有现有的行和未来没有指定“阶段”的行都将获得“-”作为值。 - cfstras

@cfstras 但是这怎么可能？'-'在SQL中是一种特殊/魔法数字吗？我真的不明白为什么它会是一个字符而不是整数，或者那个字符是否被转换为整数？没有解释似乎很奇怪。 - slow

@slow 很好的问题 - 我猜你需要在测试表上尝试一下。猜测可能会保存“-”的ASCII数字值，或者保存“-0”（因此为0），或者出现错误。也许回答者只是想告诉你考虑一个合理的默认值并将其放在那里。 - cfstras

Answer 5

其他SQL实现也有类似的限制。原因是添加列需要添加该列的值（逻辑上，即使没有物理上），这些值默认为NULL。如果不允许NULL，并且没有default，那么值将是什么？

由于SQL Server支持ADD CONSTRAINT，我建议使用Pavel的方法创建可空列，然后在填充非NULL值后添加NOT NULL约束。

- dan04

---

MySQL支持将非空列添加到带有数据的现有表中，其中对于数据类型而言，“合理”的空值会提供给现有行（例如浮点数为0.0，整数为0，字符串为空字符串等）。 - Michael Tsang

Answer 6

这对我很有用，还可以从设计视图“借用”，进行更改 -> 右键单击 -> 生成变更脚本。

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

- pabbie

Answer 7

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

- ajoejoseph

Answer 8

Alter TABLE 'TARGET' add 'ShouldAddColumn' Integer Not Null default "0"

- AKronis1369

---

这个答案对这个话题有什么补充？ - barbsan