Explain Default Constraint in SQL with Example and Uses

(Last Updated On: February 9, 2017)

DEFAULT constraint in SQL is used to insert an automatic default value into a column of a table.  If we don’t use default value constraint, then value of column will be NULL.

We will answer this question with default constraint in SQL server with example and explanation that how to use it and when to use default.

If a column of a table in SQL database can accept a NULL value and if we don’t assign a value to that column during populating the record in the table, then a NULL value will be assigned for it.  See the table below. Column MaritalStatus of table PERSON will have NULL value if we don’t populate this column with marital status ‘Married’ or ‘Unmarried’.

Id name MaritalStatus
1 JAMES Married
2 Donald NULL
3 Linda Unmarried
4 Lisa NULL
5 Alex NULL

Now, if we apply default constraint on the column MaritalStatus of a table and if we don’t supply a value to that column, a default value will be automatically inserted. For example, in below query we have applied default constraint with value ‘’Unknown’ while creating the table PERSON.

 

So, if we don’t supply value ‘Married’ or ‘Unmarried’ during insertion of record for this column, default value ‘’Unknown’ will be set automatically instead of NULL.

Id name MaritalStatus
1 JAMES Married
2 Donald Unknown
3 Linda Unmarried
4 Lisa Unknown
5 Alex Unknown
When to Use Default constraint in SQL:
  • Want readable and meaningful data into the column of a table?
  • Readable data into the log when user is inserting row for log analysis
  • Want to set automatic date and time etc. for example, date DEFAULT GETDATE() on same data column?

NOTES:

How to drop default constraint in SQL?

Answer: To drop a default constraint, already applied to a column in a table. First we need to alter the table then the column and apply DROP.

In the above table if we want to drop the default constraint on column Marital Status then below are queries (SQL Server and Oracle)

NOTE: In MySQL, we don’t need to write COLUMN e.g.