SQL 使用默认值将可空列更改为 NOT NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3186760/
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
Change a Nullable column to NOT NULL with Default Value
提问by Jim B
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()).
我今天遇到了一个旧表，其中有一个名为“Created”的日期时间列，它允许空值。现在，我想更改它，使其不为 NULL，并且还包含一个约束以添加默认值 (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
Is there any way to also specify the default value as well on the ALTER statement?
有没有办法在 ALTER 语句中也指定默认值？
回答by Abe Miessler
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 addinga 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
回答by Gage
You may have to first update all the records that are null to the default value then use the alter table statement.
您可能必须首先将所有空记录更新为默认值，然后使用 alter table 语句。
Update dbo.TableName
Set
Created="01/01/2000"
where Created is NULL
回答by Jinna Balu
you need to execute two queries:
您需要执行两个查询：
One - to add the default value to the column required
一 - 将默认值添加到所需的列
ALTER TABLE 'Table_Name` ADD DEFAULT 'value' FOR 'Column_Name'
ALTER TABLE 'Table_Name` 添加 DEFAULT 'value' FOR 'Column_Name'
i want add default value to Column IsDeleted as below:
我想为 Column IsDeleted 添加默认值，如下所示：
Example: ALTER TABLE [dbo].[Employees] ADD Default 0 for IsDeleted
示例：ALTER TABLE [dbo].[Employees] ADD IsDeleted 默认为 0
Two - to alter the column value nullable to not null
二 - 将可空的列值更改为非空
ALTER TABLE 'table_name' ALTER COLUMN 'column_name' 'data_type' NOT NULL
ALTER TABLE 'table_name' ALTER COLUMN 'column_name' 'data_type' NOT NULL
i want to make the column IsDeleted as not null
我想让列 IsDeleted 不为空
ALTER TABLE [dbo].[Employees] Alter Column IsDeleted BIT NOT NULL
ALTER TABLE [dbo].[Employees] 更改列 IsDeleted BIT NOT NULL
回答by BenW
If its SQL Server you can do it on the column properties within design view
如果是 SQL Server，您可以在设计视图中的列属性上执行此操作
Try this?:
尝试这个？：
ALTER TABLE dbo.TableName
ADD CONSTRAINT DF_TableName_ColumnName
DEFAULT '01/01/2000' FOR ColumnName
