在sql server中的现有表中添加不可为空的列? [英] Add non-nullable columns to an existing table in sql server?
问题描述
我已经有一个包含数据的表。我需要alter table添加两个不是null的新列。
I already have a table which consists of data. I need to alter 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):
-
添加了新列EmpFlag(bit,null),CreatedDate(datetime,
null)
Added new columns 'EmpFlag' (bit, null), 'CreatedDate' (datetime, null)
更新了表中的EmpFlag列,以具有一些有效值。 (只是想在一个字段上工作,所以我没有更新CreatedDate字段)
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.
当我尝试保存时,系统显示此错误讯息:
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.
推荐答案
您只需在新列中设置默认值即可添加。
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())
或这个varchar字段。
or this one for a varchar field.
alter table table_name
add column_name varchar(10) not null
constraint DF_Default_Object_Name default ('A')
alter table table_name
drop constraint DF_Default_Object_Name
这篇关于在sql server中的现有表中添加不可为空的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!