What is Default Constraint in SQL?

The DEFAULT constraint provides a default or a fixed value into the column of a table and gets overridden when the user enters a new value. The DEFAULT function is used when you insert a new row into the table without specifying any value for the column. After adding the DEFAULT constraint to the column of the table, each time when the user does not provide the value of the column then, the default value gets inserted.

In this post, you will learn about the DEFAULT constraint in SQL and how you can use it to insert a default value into a column of a table with the help of examples and use cases.

How to Add Default Constraints in SQL?

DEFAULT constraint in SQL can be added to the table in two ways:

1. While creating a table, we can add the DEFAULT constraint to the column of a table.

Suppose you want to create an employee table, having ID, NAME, and SALARY as the fields, and you don't want the SALARY field to be empty in any case, so we will specify the DEFAULT salary for each employee with the help of the DEFAULT constraint in SQL.

The syntax for adding the DEFAULT constraint while creating a table is:

Here, we have created a table named employee, having some columns as id, name, and salary. Also, you can see that against the column name, we have mentioned the data type of that column, and after that, we have written the constraint name that we want to apply to that column.

For the id column, we have added the NOT NULL and the PRIMARY KEY constraints, which are stating that the employee id can't be NULL and should behave as a PRIMARY KEY. Read more about NOT NULL in this article.

Let's see the salary field, where we have specified its data type as DECIMAL and have added the constraint of DEFAULT value. We have provided the default salary value of each employee as 10000.0, so for the cases, where we don't have the salary value of any employee, then for that employee, the default salary of 10000.0 rupees will be added.

Now, let's see the second way to add the DEFAULT constraint to the table.

2. If the employee table has already been created, then to add a DEFAULT constraint to the SALARY column, you would write a query like the one which is shown below:

Here, we have modified the already created table column SALARY, and have added the DEFAULT constraint of having the fixed default salary of each employee as 10000.0 rupees.

How to Remove Default Constraints in SQL?

To remove an existing DEFAULT constraint on any column, you can use the ALTER TABLE statement along with the DROP keyword in SQL. ALTER TABLE is used because the table is being modified or altered, and DROP is used to remove the constraint from the column of the table.

The syntax for dropping the DEFAULT constraint is:

You can use the following SQL query to drop the constraint in the employee table:

This will remove the DEFAULT constraint of the SALARY column in the employee table.

How to Insert Values for Default Constraint in SQL?

Let's take the above example of the employee table, where we have added the DEFAULT constraint to the SALARY column. Now, let's see how we can insert the values for the default constraint in SQL.

See the following INSERT statements that will add new employee details to the employee table:

Now, let's see how our table's data looks by using the SELECT command.

Examples for Default Constraint in SQL:

Let's take an example of building the table and dealing with the DEFAULT constraint from very scratch.

Let's create an employees table, having employee_id, employee_name, joining_date, location, and salary as the fields.

The syntax for creating the table is:

Now, let's insert some data into our table:

Now, let's see how our table's data looks by using the SELECT command.

Output:

As we can see from the output table above, for the rows where the data for the joining_date has not been provided, we have added the GETDATE() function as the default value, which returns the current date and time. For the location field, we have added the default location for the employees as Chennai, and for the salary field, the default salary is 10000.00.

Let's suppose we want to add a new column as employee_retirement_age, along with the default value as 60.

To add a column in a table, we can use the following syntax:

In our case,

Now, let's insert some more data into our table:

Now, let's see how our table's data looks by using the SELECT command.

Output: