SQL | DEFAULT Constraint

In SQL, DEFAULT constraint in SQL is used to provide a default value for a column when no value is specified during an INSERT operation. If a column has a DEFAULT constraint and no value is explicitly provided during the insertion of a record, the database will automatically insert the default value defined for that column.

Let’s create a table and use the DEFAULT constraint for the Location column, ensuring that a default value of 'Noida' is inserted when no value is provided.

Query:

CREATE TABLE Geeks (
    ID INT NOT NULL,
    Name VARCHAR(255),
    Age INT,
    Location VARCHAR(255) DEFAULT 'Noida'
);

-- Explicit value
INSERT INTO Geeks (ID, Name, Age, Location) VALUES (4, 'Mira', 23, 'Delhi');

-- Using the DEFAULT constraint
INSERT INTO Geeks (ID, Name, Age, Location) VALUES (5, 'Hema', 27);

-- Explicit value again
INSERT INTO Geeks (ID, Name, Age, Location) VALUES (6, 'Neha', 25, 'Delhi');

-- Using DEFAULT constraint again
INSERT INTO Geeks (ID, Name, Age, Location) VALUES (7, 'Khushi', 26, DEFAULT);

Output:

In this query:

• Table Creation: Geeks table has columns ID (NOT NULL), Name, Age, and Location with a default value 'Noida'.
• Inserts with Explicit Values: Rows for Mira and Neha provide all column values explicitly.
• Inserts Using DEFAULT: Rows for Hema and Khushi use the default 'Noida' for Location when no value or DEFAULT keyword is used.

Syntax :

CREATE TABLE table_name (
  column1 datatype DEFAULT default_value,
  column2 datatype DEFAULT default_value
);

In the above syntax:

• CREATE TABLE table_name — creates a new table.
• columnN datatype — defines each column’s name and data type.
• DEFAULT default_value — sets the value to use when an INSERT omits that column or uses the DEFAULT keyword.

Dropping the DEFAULT Constraint

If you no longer want a column to use a default value, you can drop the DEFAULT constraint. This will only apply to new rows and will not affect existing data in the table.

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:

Suggested Quiz

Edit Quiz

5 Questions

What is the main purpose of a DEFAULT constraint in SQL?

• A

  To prevent duplicate values always

• B

  To auto-fill value when missing

• C

  To convert NULL into empty string

• D

  To enforce uniqueness on column

Explanation:

DEFAULT inserts an automatic value when no explicit value is provided during an INSERT.

When does SQL apply the DEFAULT value to a column?

• A

  Only when NULL is inserted

• B

  Only when UPDATE is executed

• C

  When column value is omitted

• D

  When table has no primary key

Explanation:

DEFAULT triggers only when the INSERT statement does not supply a value for that column.

What happens after dropping a DEFAULT constraint?

• A

  New rows get no default value

• B

  Old rows get updated instantly

• C

  All NULL values get auto-fixed

• D

  New rows keep using old default

Explanation:

Dropping DEFAULT affects only future inserts; existing rows remain unchanged.

Which keyword explicitly forces use of a DEFAULT value?

• A

  REQUIRED keyword in SQL

• B

  DEFAULT keyword in SQL

• C

  MISSING keyword in SQL

• D

  AUTOFILL keyword in SQL

Explanation:

Using the DEFAULT keyword in an INSERT forces the column to use its defined default value.

What happens if a DEFAULT constraint exists but a value is provided?

• A

  Default overrides user value

• B

  SQL rejects the provided value

• C

  Column gets stored as NULL only

• D

  User value replaces the default

Explanation:

When a value is supplied explicitly, SQL uses it instead of the default.

Quiz Completed Successfully

Your Score :   2/5

Accuracy :  0%

Login to View Explanation

1/5 1/5 < Previous Next >