Add default value to existing column (SQL)

In SQL Server there are two ways to add a column with a default value.



Add Default Value to Existing Column




-- Add default to existing column DateOfHire:




ALTER TABLE [dbo].[Employees] ADD  DEFAULT (getdate()) FOR[DateOfHire]







-- Add default value to existing column IsTerminated




ALTER TABLE [dbo].[Employees] ADD  DEFAULT ((0)) FOR[IsTerminated]







Add New Column with Default Value




-- Add new column DateOfHire with default




ALTER TABLE Employees ADD DateOfHire datetime DEFAULT(GETDATE())







-- Add new column IsTerminated with default




ALTER TABLE Employees ADD IsTerminated datetime DEFAULT (0)







Add Default Value with Create Table




CREATE TABLE [dbo].[Employees]




(




    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,




    [FirstName] [varchar](50) NULL,




    [LastName] [varchar](50) NULL,




    [SSN] [varchar](9) NULL,




    -- Add default of zero




    [IsTerminated] [bit] NOT NULL DEFAULT ((0)) ,




    -- Add default of getdate()




    [DateAdded] [datetime] NULL DEFAULT (getdate()),




    [Comments] [varchar](255) NULL,




    [DateOfHire] [datetime] NULL





)





 

               or

ALTER TABLE TableName WITH NOCHECK
ADD CONSTRAINT DF_DefaultName DEFAULT 'Default Value' FOR ColumnName

ALTER TABLE yourTable ADD  CONSTRAINT constraintName  
   DEFAULT ('XYZ') FOR [YourColumn]
To change the default, drop the constraint and re-add it with the new value:

ALTER TABLE yourTable
DROP CONSTRAINT constraintName

ALTER TABLE yourTable ADD  CONSTRAINT constraintName  
   DEFAULT ('ABC') FOR [YourColumn]