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.

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.

Syntax :

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

In the above syntax:

• CREATE TABLE table_name — creates a new table.
• columnN datatype — defines each column’s name and data type.
• DEFAULT default_value — sets the value to use when an INSERT omits that column or uses the DEFAULT keyword.

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: