Change a Nullable column to NOT NULL with Default Value

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try this  ALTER TABLE table name ALTER COLUMN col name data type NOT NULL

[sql] Change a Nullable column to NOT NULL with Default Value

Examples related to sql

Examples related to sql-server

Examples related to alter-table

Examples related to alter-column