Default Constraint in SQL Server

We use the Default Constraint in SQL Server to Specify Default Values for a Column. SQL Server uses the default value when the value for that column is absent in the insert query.

Without Default Constraint

The following scripts create the Employee Table without any default constraint.

CREATE TABLE Employee (
	EmployeeID  [int]         ,
	FirstName   [varchar](50) ,
	LastName    [varchar](50) ,
	Department  [varchar](20) ,
)

We insert a row into the table. But the insert query does not contain any value for the Department column. Since the Department is a Nullable column, the SQL Server inserts the NULL into the Department.

insert into Employee (EmployeeID, FirstName, LastName) 
values (1,'Olive','Yew')

Select * from Employee


EmployeeID    FirstName    LastName    Department
----------    ---------    --------    ----------
1             Olive        Yew         NULL

Note that if the Department is a Non Nullable column then the SQL Server will throw an error.

Default Constraint

To Provide a Default Value for a column use the keyword Default followed by default value. For a string values use the single quote (Double quotes not allowed). Do not use quotes for numbers.

In the following example, We specify the Admin as the default for the department column

CREATE TABLE Employee (
	EmployeeID  [int]         ,
	FirstName   [varchar](50) ,
	LastName    [varchar](50) ,
	Department  [varchar](20) Default 'Admin' ,
)

The following insert query does not provide any value to the Department column. Hence the SQL Server inserts the default value of Admin.

//No Value for Department. Admin is inserted
insert into Employee (EmployeeID, FirstName, LastName) 
values (1,'Olive','Yew')

The SQL Server uses the default value only when if we do not provide any value during the insertion. If we insert null, then it will insert the null value and not default. But if the column does not allow null, then it will throw the error.

//null for Department. Null is stored as it is. Default is not used
insert into Employee (EmployeeID, FirstName, LastName, Department) 
values (2,'Aida','Bugg',null)


Select * from Employee


EmployeeID    FirstName    LastName    Department
----------    ---------    --------    ----------
1             Olive        Yew         Admin
2             Aida         Bugg	       NULL

Important Rules of Default Constraint

Use the Single quote marks for strings.

//Admin as the defulat Department
Department  [varchar](20) Default 'Admin'


//Empty Space as the default
Department  [varchar](20) Default ''

Do not use double quote marks.

//Do not use the double quote

Department  [varchar](20) Default "Admin"

//Throws the Error
The name "Admin" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

To make the number the default value enter the number without quotes.

//Numeric values as default values

Amount int Default 0

Rate int Default 100

Price decimal(9,2) Default 10.50

You can make use of Brackets

CREATE TABLE Test (
  Department  [varchar](20) Default ('Admin'),
  Amount int Default (0),
  Price decimal(9,2) Default (10.50)
)

Functions as Default Value

You can also use the system functions or user defined functions as default value.

In the following example, we use getDate function as the default value for the DateCreated to column.

CREATE TABLE Employee (
	EmployeeID  [int]         ,
	FirstName   [varchar](50) ,
	DateCreated [DateTime2] Default getDate()
)


insert into Employee (EmployeeID, FirstName) 
values (1,'Olive')

insert into Employee (EmployeeID, FirstName) 
values (2,'Aida')

Select * from Employee



EmployeeID   FirstName  DateCreated 
------------ ---------- ---------------------------
1            Olive      2021-08-19 19:31:00.6630000
2            Aida       2021-08-19 19:31:00.6630000

Naming the Default Constraint

All constraints gets a name in SQL Server. If we do not provide any name then SQL Server will automatically generate one for us.

You can name the default constraint inline as shown below.

CREATE TABLE Employee (
    ...
    ... 
    Department  [varchar](20)  CONSTRAINT DF_Department  DEFAULT 'Admin'
    ...
    ...
)

Finding the Default Constraint Name

The following query retrieves the name of the Defalt constraint of a particular column.

DROP TABLE Employee

CREATE TABLE Employee (
	EmployeeID  [int]         ,
	FirstName   [varchar](50) ,
	LastName    [varchar](50) ,
	Department  [varchar](20) DEFAULT ('Admin')
)



SELECT default_constraints.name
FROM   sys.all_columns 
INNER JOIN  sys.tables  
  ON all_columns.object_id = tables.object_id
INNER JOIN  sys.schemas 
  ON tables.schema_id = schemas.schema_id
INNER JOIN  sys.default_constraints  
   ON all_columns.default_object_id = default_constraints.object_id
WHERE     schemas.name = 'dbo' 
      AND tables.name = 'Employee' 
      AND all_columns.name = 'Department'


**Result****


name
------------------------------
DF__Employee__Depart__29572725

Drop Default Constraint

Since the Default value is a Constraint, we delete it just like we delete any other Constraint. But first, we need to find the name of the Constraint using the query from the previous section.

Once you have the name of the Constraint you can delete it using the alter table drop Constraint syntax

//Syntax
Alter table <tableName> DROP Constraint <constrain-name>



//Example
Alter table Employee DROP Constraint DF__Employee__Depart__29572725

Adding the Default to Existing Table

To add a default constraint to an existing column use the Alter Table query. The basic syntax is as follows

ALTER TABLE <tableName>
  ADD CONSTRAINT <constraintName> DEFAULT <default> FOR <columnName>;

The following example sets the empty string as default value for the Department column in the Employee table. DF_Employee_Department is the name of the constraint.

ALTER TABLE Employee
  ADD CONSTRAINT DF_Employee_Department DEFAULT '' FOR Department;

Modifying the Default Constraint

To Modify the default constraint of an existing column follow these steps

1. Find out the name of the current default constraint.
2. Drop the constraint.
3. Add the new constraint.

Reference

Document