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.
Table of Contents
Without Default Constraint
The following scripts create the Employee Table without any default constraint.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 8 9 10 11 | 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
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 | //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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | //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.
1 2 3 4 5 6 7 8 | //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.
1 2 3 4 5 6 7 8 9 | //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.
1 2 3 4 5 6 7 8 9 | //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
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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.
1 2 3 4 5 6 7 8 9 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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
1 2 3 4 5 6 7 8 9 | //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
1 2 3 4 | 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.
1 2 3 4 | 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
- Find out the name of the current default constraint.
- Drop the constraint.
- Add the new constraint.
