Skip to content
Related Articles

Related Articles

SQL | DEFAULT Constraint

View Discussion
Improve Article
Save Article
  • Difficulty Level : Easy
  • Last Updated : 28 Aug, 2020
View Discussion
Improve Article
Save Article

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;

IDNameAgeLocation
4Mira23Delhi
5Hema27Noida
6Neha25Delhi
7Khushi26Noida


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;

IDNameAgeLocation
4Mira23Delhi
5Hema27Noida
6Neha25Delhi
7Khushi26Noida
8Komal24Delhi
9Payal26NULL

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!