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:

CREATE TABLE books ( book_id INT NOT NULL PRIMARY KEY, title varchar(255) NOT NULL, pubdate date NOT NULL, isbn varchar(13) DEFAULT '1-84356-028-3', author_id INT NOT NULL )
Code language: PHP (php)

The isbn column in the books table accepts ‘1-84356-028-3’ as the default value. If we insert a new row into the  books table without specifying the 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 adds a new book to the books table:

INSERT INTO books(title,pubdate,author_id) VALUES('SQL Tutorial','2010-01-01',1);
Code language: JavaScript (javascript)

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

SELECT * FROM books;
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:

ALTER TABLE books ADD CONSTRAINT df_pubdate DEFAULT GETDATE() FOR pubdate
Code language: PHP (php)

Removing SQL DEFAULT constraint

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

ALTER TABLE table ALTER COLUMN column DROP DEFAULT;
Code language: PHP (php)

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

ALTER TABLE books ALTER COLUMN title DROP DEFAULT;
Code language: PHP (php)

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