Explain Default Constraint in SQL with Example and Uses

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

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.

CREATE TABLE PERSON (
	 Id int NOT NULL,
	 Name varchar(255) NOT NULL,
	 MaritalStatus varchar(255) DEFAULT 'Unknown'
 )

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.

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)

ALTER TABLE PERSON

ALTER COLUMN MaritalStatus DROP DEFAULT

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

“ALTER MaritalStatus DROP DEFAULT”