将可空列更改为具有默认值的非空 [英] Change a Nullable column to NOT NULL with Default Value

问题描述

我今天遇到了一张旧表，其中有一个名为已创建的日期时间列，该列允许为空。现在，我想更改它，使其不为NULL，并且还包含一个约束以添加默认值（getdate（））。

I came across an old table today with a datetime column called 'Created' which allows nulls. Now, I'd want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()).

到目前为止我有以下脚本，只要我事先清除了所有空值，该脚本就可以很好地工作：

So far I've got the following script, which works fine provided that i've cleaned up all the nulls beforehand:

ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL 

还有什么方法可以同时指定默认值ALTER语句？

Is there any way to also specify the default value as well on the ALTER statement?

推荐答案

我认为您需要将其作为三个单独的语句来执行。我一直在环顾四周，我所见过的一切似乎都建议您在添加列时可以这样做，但如果要更改一个列则不能。

I think you will need to do this as three separate statements. I've been looking around and everything i've seen seems to suggest you can do it if you are adding a column, but not if you are altering one.

ALTER TABLE dbo.MyTable
ADD CONSTRAINT my_Con DEFAULT GETDATE() for created

UPDATE MyTable SET Created = GetDate() where Created IS NULL

ALTER TABLE dbo.MyTable 
ALTER COLUMN Created DATETIME NOT NULL 

这篇关于将可空列更改为具有默认值的非空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！