SQL | DEFAULT Constraint

The DEFAULT Constraint is used to fill a column with a default and fixed value. The value will be added to all new records when no other value is provided.

1. Using DEFAULT on CREATE TABLE :

Syntax :

CREATE TABLE tablename (
Columnname DEFAULT 'defaultvalue' );

Example –
To set a DEFAULT value for the “Location” column when the “Geeks” table is created –

CREATE TABLE Geeks (
ID int NOT NULL,
Name varchar(255),
Age int,
Location varchar(255) DEFAULT 'Noida');
INSERT INTO Geeks VALUES (4, 'Mira', 23, 'Delhi');
INSERT INTO Geeks VALUES (5, 'Hema', 27);
INSERT INTO Geeks VALUES (6, 'Neha', 25, 'Delhi');
INSERT INTO Geeks VALUES (7, 'Khushi', 26);

Output –



select *
from Geeks;

ID Name Age Location
4 Mira 23 Delhi
5 Hema 27 Noida
6 Neha 25 Delhi
7 Khushi 26 Noida


2. DROP a DEFAULT Constraint :

Syntax :

ALTER TABLE tablename
ALTER COLUMN columnname 
DROP DEFAULT;

Example –

ALTER TABLE Geeks
ALTER COLUMN Location
DROP DEFAULT;

Let us add 2 new rows in Geeks table :

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

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

Output –

Select * 
from Geeks;

ID Name Age Location
4 Mira 23 Delhi
5 Hema 27 Noida
6 Neha 25 Delhi
7 Khushi 26 Noida
8 Komal 24 Delhi
9 Payal 26 NULL

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.