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.

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.

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

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

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.

Important Rules of Default Constraint

Use the Single quote marks for strings.

Do not use double quote marks.

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

You can make use of Brackets

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.

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.

Finding the Default Constraint Name

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

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

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

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.

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

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top