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?
This question is related to
sql
sql-server
alter-table
alter-column
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
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
Try this
ALTER TABLE table_name ALTER COLUMN col_name data_type NOT NULL;
Source: Stackoverflow.com