SQL DEFAULT Constraint

Summary: in this tutorial, you will learn you how to use the SQL DEFAULT constraint to insert a default value into a column.

The DEFAULT constraint inserts a default value into a column of a table when you insert a new row into the table without specifying the value for the column.

Creating SQL DEFAULT constraint

There are two ways to create DEFAULT constraints for columns:

You can assign a DEFAULT constraint to a column in the  CREATE TABLE statement as the following statement:

The isbn column in the books table accepts ‘1-84356-028-3’ as the default value. If we to insert a new row into the  books table without specifying value for ISBN column, the database engine will insert the value 1-84356-028-3 into the isbn column. See the following INSERT statement that add a new book to the books table:

We can query the books table by using SELECT statement to see what value has been inserted into the isbn column:

SQL DEFAULT constraint

The column ISBN stores the default value ‘1-84356-028-3’ specified in the table definition.

The DEFAULT constraint accepts not only literal value but also a value returned by a function. For example, we assign today date as the default value for the pubdate column by using the following statement:

Removing SQL DEFAULT constraint

To remove an existing DEFAULT constraint, you also use the  ALTER TABLE statement as follows:

For example, to remove the DEFAULT constraint of the title column in the books table, you use the following statement:

In this tutorial, we’ve shown you how to use the SQL DEFAULT constraint to insert a default value into a column.