DEFAULT Constraint in SQL

The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table. Lets see how to specify this constraint and how it works.

Specify DEFAULT constraint while creating a table

Here we are creating a table “STUDENTS”, we have a requirement to set the exam fees to 10000 if fees is not specified while inserting a record (row) into the STUDENTS table. We can do so by using DEFAULT constraint. As you can see we have set the default value of EXAM_FEE column to 10000 using DEFAULT constraint.

CREATE TABLE STUDENTS(
       ROLL_NO   INT           NOT NULL,
       STU_NAME VARCHAR (35)   NOT NULL,
       STU_AGE  INT            NOT NULL,
       EXAM_FEE INT            DEFAULT 10000,
       STU_ADDRESS  VARCHAR (35) ,     
       PRIMARY KEY (ROLL_NO)
);

Specify DEFAULT constraint while creating a table

What if we want to set this constraint on a already existing table. For this we can ALTER Table statement like this:

Syntax:

ALTER TABLE <table_name>
   MODIFY <column_name> <column_data_type> DEFAULT <default_value>;

Example:

ALTER TABLE STUDENTS
   MODIFY EXAM_FEE INT DEFAULT 10000;

This way we can set constraint on a already created table.

How to drop DEFAULT Constraint

In the above sections, we have learnt the ways to set Constraint. Here we will see how to drop (delete) a Constraint:

Syntax:

ALTER TABLE <table_name>
  ALTER COLUMN <column_name> DROP DEFAULT;

Example:
Lets say we want to drop the constraint from STUDENTS table, which we have created in the above sections. We can do it like this.

ALTER TABLE CUSTOMERS
   ALTER COLUMN EXAM_FEE DROP DEFAULT;