SQL DEFAULT Constraint - Insert a Default Value Into a Columm

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:

Use CREATE TABLE statement if the table is new
Use ALTER TABLE statement for an existing table.

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;

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 pubdateCode 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.