'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?

sqlsql-serverdefault-valuealter-tablealter-column


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

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

Solution 4:[4]

Try this

ALTER TABLE table_name ALTER COLUMN col_name data_type NOT NULL;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jeremy Stein
Solution 2 Jinna Balu
Solution 3 marc_s
Solution 4 belal ahmad

Related Questions