更改列: 从null到not null

Question

我有一张表格，其中有几个可空的整数列。这是不理想的，因此我希望将所有的空值更新为0，然后将这些列设置为NOT NULL。除了将空值更改为0之外，必须保留数据。

我正在寻找将某个列（称之为ColumnA）修改为“not null”的具体SQL语法。假设数据已经更新，不包含空值。

使用 SQL Server 2000。

- Mike

---

还有一件事 - 你可能想要添加一个默认值，以便任何没有指定列的现有插入操作不会失败：ALTER TABLE FOO ADD CONSTRAINT FOO_Bar_Default DEFAULT 0 FOR Bar。 - Marc Gravell

也许你会感到惊讶，根据某些情况，在将列更改为NOT NULL时会产生大量的日志记录。 - Martin Smith

Answer 1

首先，让所有当前的NULL值消失：

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL

然后，更新表定义以禁止“NULL”：

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

- mdb

---

让我为前面两个正确的回答增加一些价值。NULL的语义可以有几种含义，其中常见的一种是“未知”。 以分数SCORE为例，一个空分数和一个零分数是有很大区别的！在你采纳以上建议之前，请确保你的应用程序不会在业务逻辑中使用null。 - TechTravelThink

先备份您的数据库，以防您打错字而导致数据库崩溃。 - mpen

数据库应该经常备份。因为你永远不知道你的员工何时可能会写一条UPDATE或DELETE语句，却忘记了加上WHERE子句。 - Vivian River

我知道这个问题是针对SQLServer的，但是对于Postgres 9来说并不起作用。相反，可以尝试使用以下语句："ALTER TABLE [表名] ALTER COLUMN [列名] SET NOT NULL"。 - John Bowers

你必须重新输入该列（假设我不想查找它们所有）还是可以省略该部分？ - blindguy

Answer 2

我遇到了同样的问题，但是该字段以前默认为null，现在我想将其默认为0。这需要在mdb的解决方案之后添加一行代码：

ALTER TABLE [Table] ADD CONSTRAINT [Constraint] DEFAULT 0 FOR [Column];

- Greg Dougherty

---

这并没有解决问题。默认值已经设置好了，需要改变的是 NOT NULL。只有你的答案，所有现有记录仍将保持为 NULL，问题仍然存在。 - PandaWood

你读到关于“添加一行”的部分了吗？也就是说，这是在上面的答案基础上额外增加的内容？ - Greg Dougherty

实际上，“再加一行”并不清楚地表示“除了上面的答案之外还要再多添加一行”（尤其是因为上面有很多个答案）——如果这是你的意思，那么措辞确实需要更改，并且你应该包括你所指的答案的那一行。 - PandaWood

只是想说，明确命名约束条件（包括默认值）是一个好主意。如果你需要删除一列，你必须知道要删除的约束条件名称才能执行删除操作。我们的数据库迁移中遇到过这样的问题。虽然例子中有明确命名，但很多开发人员依然会犯错，因为名称并非必需。 - jocull

Answer 3

你需要分两步来完成:

1. 更新表，以使列中没有空值。

UPDATE MyTable SET MyNullableColumn = 0
WHERE MyNullableColumn IS NULL

2. 修改表以更改列的属性

ALTER TABLE MyTable
ALTER COLUMN MyNullableColumn MyNullableColumnDatatype NOT NULL

- Ralph Wiggum

---

如果您现有的列是数字列，则SET MyNullableColumn = 0才有效。对于非数字类型，您需要设置另一个默认值，如空字符串或默认日期等。但是，这个答案完全符合上述问题并且是正确的;-) - Elyas Hadizadeh

Answer 4

对于Oracle 11g，我可以按以下方式更改列属性：

ALTER TABLE tablename MODIFY columnname datatype NOT NULL;

否则，abatichev的答案看起来很不错。您不能重复执行alter语句 - 它会抱怨（至少在SQL Developer中）该列已经不为null。

- JDM

---

在Oracle 11中，似乎不需要重复datatype。仅使用columname NOT NULL就足够了。请参阅文档。 - jpmc26

Answer 5

这对我有用：

ALTER TABLE [Table] 
Alter COLUMN [Column] VARCHAR(50) not null;

- Dheeraj Sam

Answer 6

只要该列不是唯一标识符。

UPDATE table set columnName = 0 where columnName is null

然后

修改表并将该字段设置为非空，并指定默认值为0

- Eppz

Answer 7

如果存在FOREIGN KEY CONSTRAINT，那么如果主键表中的列中没有'0'，就会出现问题。 解决方法是...

步骤1：

使用以下代码禁用所有约束：

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

第二步：

RUN UPDATE COMMAND (as mentioned in above comments)
RUN ALTER COMMAND (as mentioned in above comments)

步骤三：

使用以下代码启用所有约束条件：

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

- sam05

Answer 8

这个看起来更简单，但只适用于Oracle：

ALTER TABLE [Table] 
ALTER [Column] NUMBER DEFAULT 0 NOT NULL;

此外，通过这种方式，您不仅可以更改表格属性，还可以添加列。

如果该值为空，则在此示例中更新为默认值（0）。

- csomakk

Answer 9

在我的情况下，我遇到了答案不清晰的困难。最终我使用了以下方法：

ALTER TABLE table_name CHANGE COLUMN column_name column_name VARCHAR(200) NOT NULL DEFAULT '';

将VARCHAR(200)更改为您的数据类型，可选择更改默认值。

如果没有默认值，您在进行此更改时会遇到问题，因为默认值将为null，从而创建冲突。

- Philip Kirkbride

Answer 10

使列不为空并添加默认值也可以在SSMS GUI中完成。

其他人已经说过，直到所有现有数据都为“非空”才能设置“非空”，如下所示： UPDATE myTable SET myColumn = 0 完成此操作后，在表设计视图中(右键单击表，然后单击“设计视图”)，您只需取消选中允许空值列即可。

在设计视图中选择该列后，您可以在下方窗口中看到列属性，并将默认值设置为0，如下所示：

- Tony L.