Summary -

In this topic, we described about the below sections -

Why DEFAULT Constraint?

While inserting multiple rows on table, we may need to insert same value multiple times for a specific column. The repeated value may be default value or the most used value. SQL supports a feature of having the default or most repeated value as a default value. DEFAULT statement used to default the column in the table.

How to add DEFAULT Constraint?

DEFAULT constraint used to set default values to columns when not specified in INSERT statement. We can add DEFAULT Constraint to a column in two ways. Those are –

  • Using CREATE TABLE statement
  • Using ALTER TABLE statement

Note :- The default value is inserted to the column with DEFAULT constraint only when the column value not specified in INSERT statement.

Syntax for adding DEFAULT constraint while creating table – 

CREATE TABLE table_name (
     column1 datatype [DEFAULT default_value1],
     column2 datatype [DEFAULT default_value2],
        …,   
     columnN datatype [DEFAULT default_valueN]);

Syntax for adding DEFAULT constraint on existing table – 

ALTER TABLE table_name
ALTER COLUMN column_name
SET DEFAULT default_value;

Syntax for deleting DEFAULT constraint on existing table - 

ALTER TABLE table_name
ALTER COLUMN column _name
DROP DEFAULT;

Examples

Scenario – Creating DEFAULT constraint while creating table.

Requirement – Create employee_details table with dept_id default value 1000. The query was as follows -

CREATE TABLE employee_details (
    emp_id INT NOT NULL,
    emp_name VARCHAR (20) NOT NULL, 
    dept_id INT DEFAULT 1000);

By executing above query, we can create employee_details table with default dept_id as 1000.


Scenario – Verifying DEFAULT constraint created or not on table.

Requirement – insert employee_details row without dept_id. The query was as follows - 

INSERT INTO employee_details VALUES (001, "Employee1");

By executing above query, we can get output shown below –

emp_id emp_name dept_id
001 Employee1 1000

Scenario – Adding DEFAULT constraint to an existing table.

Requirement – add DEFAULT constraint on dept_id of an existing employee_details table. The query was as follows – 

ALTER TABLE employee_details
ALTER COLUMN dept_id SET DEFAULT 1000;

By executing above query, we can add DEFAULT constraint on dept_id to employee_details table.


Scenario – Deleting DEFAULT constraint on existing table.

Requirement – Deleing DEFAULT constraint on dept_id of an existing employee_details table. The query was as follows – 

ALTER TABLE employee_details ALTER COLUMN dept_id DROP DEFAULT;

By executing above query, we can drop DEFAULT constraint on dept_id of existing employee_details table.