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 DEFAULTconstraint 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 DEFAULTconstraints for columns:

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

You can assign a DEFAULTconstraint 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: SQL (Structured Query Language) (sql)

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

INSERT INTO books(title,pubdate,author_id)
VALUES('SQL Tutorial','2010-01-01',1);Code language: SQL (Structured Query Language) (sql)

We can query the bookstable by using SELECT statement to see what value has been inserted into the isbncolumn:

SELECT * FROM books;Code language: SQL (Structured Query Language) (sql)

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

The DEFAULTconstraint 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 pubdatecolumn by using the following statement:

ALTER TABLE books 
ADD CONSTRAINT df_pubdate
DEFAULT GETDATE() FOR pubdateCode language: SQL (Structured Query Language) (sql)

Removing SQL DEFAULT constraint

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

ALTER TABLE table
ALTER COLUMN column DROP DEFAULT;Code language: SQL (Structured Query Language) (sql)

For example, to remove the DEFAULTconstraint of the titlecolumn in the bookstable, you use the following statement:

ALTER TABLE books
ALTER COLUMN title DROP DEFAULT;Code language: SQL (Structured Query Language) (sql)

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