将可空列更改为带默认值的 NOTNULL

今天我看到一个旧的表，其中有一个名为“ Created”的 datetime 列，它允许使用空值。现在，我想改变这一点，使其不为 NULL，并且还包括一个要添加默认值(getdate ())的约束。

到目前为止，我已经得到了下面的脚本，如果我事先清除了所有的 null，那么这个脚本就可以很好地工作:

ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL

有没有办法在 ALTER 语句中也指定默认值？

184518 次浏览
小开

If its SQL Server you can do it on the column properties within design view

Try this?:

ALTER TABLE dbo.TableName
ADD CONSTRAINT DF_TableName_ColumnName
DEFAULT '01/01/2000' FOR ColumnName
小开
最佳答案

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
小开

You may have to first update all the records that are null to the default value then use the alter table statement.

Update dbo.TableName
Set
Created="01/01/2000"
where Created is NULL
小开

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'

i want add default value to Column IsDeleted as below:

Example: ALTER TABLE [dbo].[Employees] ADD Default 0 for IsDeleted

Two - to alter the column value nullable to not null

ALTER TABLE 'table_name' ALTER COLUMN 'column_name' 'data_type' NOT NULL

i want to make the column IsDeleted as not null

ALTER TABLE [dbo].[Employees] Alter Column IsDeleted BIT NOT NULL

小开

Try this

ALTER TABLE table_name ALTER COLUMN col_name data_type NOT NULL;


提交答案