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 “Geeks” table is created.
Query:
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,DEFAULT);
INSERT INTO Geeks VALUES (6, 'Neha', 25, 'Delhi');
INSERT INTO Geeks VALUES (7, 'Khushi', 26,DEFAULT);
Select * from Geeks;
Output:
DROP a DEFAULT Constraint
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: