SQL | DEFAULT Constraint

In SQL, DEFAULT constraint in SQL is used to provide a default value for a column when no value is specified during an INSERT operation. If a column has a DEFAULT constraint and no value is explicitly provided during the insertion of a record, the database will automatically insert the default value defined for that column.

Features:

• Inserts a predefined value if none is provided.
• Useful for commonly repeated values.
• Not required for every column.
• Can be applied to numbers, dates, strings, etc.
• NULL must be specified explicitly.

Syntax :

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype DEFAULT default_value,
    ...
);

In the above syntax:

• CREATE VIEW view_name: Defines a new view.
• AS : Indicates the SELECT query that defines the view.
• SELECT … : Specifies columns or expressions to include.
• FROM table_name: Selects the source table(s).
• WHERE … : Optional filter to include specific rows

Using the DEFAULT Constraint during Table Creation

Let’s create a table and use the DEFAULT constraint for the Location column, ensuring that a default value of 'Noida' is inserted when no value is provided.

Query:

CREATE TABLE Geeks (
    ID INT NOT NULL,
    Name VARCHAR(255),
    Age INT,
    Location VARCHAR(255) DEFAULT 'Noida'
);

-- Explicit value
INSERT INTO Geeks (ID, Name, Age, Location) VALUES (4, 'Mira', 23, 'Delhi');

-- Using the DEFAULT constraint
INSERT INTO Geeks (ID, Name, Age, Location) VALUES (5, 'Hema', 27);

-- Explicit value again
INSERT INTO Geeks (ID, Name, Age, Location) VALUES (6, 'Neha', 25, 'Delhi');

-- Using DEFAULT constraint again
INSERT INTO Geeks (ID, Name, Age, Location) VALUES (7, 'Khushi', 26, DEFAULT);

Output:

In this query:

• Table Creation: Geeks table has columns ID (NOT NULL), Name, Age, and Location with a default value 'Noida'.
• Inserts with Explicit Values: Rows for Mira and Neha provide all column values explicitly.
• Inserts Using DEFAULT: Rows for Hema and Khushi use the default 'Noida' for Location when no value or DEFAULT keyword is used.

Dropping the DEFAULT Constraint

If you no longer want a column to use a default value, you can drop the DEFAULT constraint. This will only apply to new rows and will not affect existing data in the table.

Syntax:

ALTER TABLE tablename
ALTER COLUMN columnname
 DROP DEFAULT;

Query: 

ALTER TABLE Geeks
ALTER COLUMN Location
DROP DEFAULT;

Let us add 2 new rows in the Geeks table :

Query:

INSERT INTO Geeks VALUES (8, 'Komal', 24, 'Delhi');
INSERT INTO Geeks VALUES (9, 'Payal', 26,NULL);

Note - Dropping the default constraint will not affect the current data in the table, it will only apply to new rows. 

Select * from Geeks;

Output: