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.

Syntax

CREATE TABLE tablename (Columnname DEFAULT ‘defaultvalue’ );

Using DEFAULT on CREATE TABLE

To set a DEFAULT value for the “Location” column when the “Beginner” table is created.

Query:

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

Output:

DROP a DEFAULT Constraint 

Syntax 

ALTER TABLE tablename

ALTER COLUMN columnname 

DROP DEFAULT;

Query: 

ALTER TABLE Beginner
ALTER COLUMN Location
DROP DEFAULT;

Let us add 2 new rows in the Beginner table :

Query:

INSERT INTO Beginner VALUES (8, 'Komal', 24, 'Delhi');
INSERT INTO Beginner 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 Beginner;

Output: