Skip to content
Related Articles

Related Articles

Improve Article

SQL | DEFAULT Constraint

  • Last Updated : 28 Aug, 2020

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

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
Recommended Articles
Page :