将不可为空的列添加到 SQL Server 中的现有表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2268513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add non-nullable columns to an existing table in SQL server?
提问by sanjeev40084
I already have a table which consists of data. I need to alter the table to add two new columns which are not null. How can I do that without losing any existing data?
我已经有一个由数据组成的表。我需要更改表以添加两个不为空的新列。我怎样才能在不丢失任何现有数据的情况下做到这一点？
Here's what I tried (via right-clicking the table and selecting Design):
这是我尝试过的（通过右键单击表格并选择设计）：
Added new columns 'EmpFlag' (bit, null), 'CreatedDate' (datetime, null)
Updated 'EmpFlag' column in the table, to have some valid values. (Just wanted to work on one field, so I didn't update 'CreatedDate' field)
Now right clicked table, design, and made it not null.
添加了新列 'EmpFlag' (bit, null), 'CreatedDate' (datetime, null)
更新了表中的“EmpFlag”列，使其具有一些有效值。（只是想在一个领域工作，所以我没有更新“CreatedDate”字段）
现在右键单击表，设计，并使其不为空。
When I tried to save, this error message appeared:
当我尝试保存时，出现此错误消息：
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.
不允许保存更改。您所做的更改需要删除并重新创建以下表。
回答by Jose Chama
You just set a default value in the new columns and that will allow you to add them.
您只需在新列中设置一个默认值，这样您就可以添加它们。
alter table table_name
add column_name datetime not null
constraint DF_Default_Object_Name default (getdate())
or this one for a varchar field.
或者这个用于 varchar 字段。
alter table table_name
add column_name varchar(10) not null
constraint DF_Default_Object_Name default ('A')
You can also drop the default if you do not need it after you added the column.
如果在添加列后不需要默认值，也可以删除默认值。
alter table table_name
drop constraint DF_Default_Object_Name
回答by Aaron Bertrand
If you don't want to place a default on the columns, you can:
如果您不想在列上放置默认值，您可以：
- create the new columns as NULLable
- UPDATE the existing data appropriately
- add the NOT NULL constraint
- 将新列创建为 NULLable
- 适当更新现有数据
- 添加 NOT NULL 约束
回答by Tony L.
Adding new NOT NULL columns with defaults can be done in the GUI like below. Changing an existing to NOT NULL appears to be a different story. I get the same message you had. One option would be to create a new NOT NULL column with a default to replace the old column and then copy the old column data to the new column data.
添加具有默认值的新 NOT NULL 列可以在 GUI 中完成，如下所示。将现有更改为 NOT NULL 似乎是另一回事。我收到了与您相同的消息。一种选择是使用默认值创建一个新的 NOT NULL 列来替换旧列，然后将旧列数据复制到新列数据。
- Put table in Design View (right click on table->select Design)
- Add column, select data type
- Uncheck Allow Nullsand set Default Value or Binding= your default values like below
- 将表格放在设计视图中（右键单击表格-> 选择设计）
- 添加列，选择数据类型
- 取消选中Allow Nulls并设置Default Value 或 Binding= 您的默认值，如下所示
