Change a Nullable column to NOT NULL with Default Value
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
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
您可能必须首先将所有空值记录更新为默认值，然后使用alter table语句。
Update dbo.TableName
Set
Created="01/01/2000"
where Created is NULL
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
上一篇: 添加一列，如果它不存在所有表？