Change Nullable column to NOT NULL with default value

Question

Today I came across an old table with a datetime column called "Created", which allows null. Now I want to change this so that it is NOT NULL, and also include a restriction to add a default value (getdate ()).

So far, I have the following script that works fine, provided that I cleared all zeros in advance:

ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL 

Can I also specify a default value in an ALTER statement?

Answer 1

I think you will need to do this as three separate statements. I look around, and everything that I saw seems to suggest that you do this if you add a column, but not if you change it.

 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 

Answer 2

You may need to update all records with a default value of zero first, and then use the alter table statement.

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

Answer 3

If its SQL Server you can do this in the column properties in the design view

Try it?:

 ALTER TABLE dbo.TableName ADD CONSTRAINT DF_TableName_ColumnName DEFAULT '01/01/2000' FOR ColumnName 

Answer 4

you need to fulfill two queries:

One is to add a default value to the required column

ALTER TABLE 'Table_Name` ADD DEFAULT' value 'FOR' Column_Name '

I want to add a default value to Column IsDeleted as shown below:

Example: ALTER TABLE [dbo]. [Employees] ADD The default value is 0 for IsDeleted

Two - change the value of a column with a zero value to zero

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