SQL DEFAULT Constraint

In SQL, the DEFAULT constraint is used to set a default value if we try to insert an empty value in a column. For example,

CREATE TABLE Colleges (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20),
  college_country VARCHAR(20) DEFAULT 'US'
);

Here, the default value of the college_country column is US.

If we try to store the NULL value in the college_country column, its value will be US by default. For example,

-- Inserts 'US' to the college_country column
INSERT INTO Colleges (college_id, college_code)
VALUES (1, 'ARP76');

-- Inserts 'UAE' to the college_country column
INSERT INTO Colleges (college_id, college_code, college_country)
VALUES (2, 'JWS89', 'UAE');

DEFAULT Constraint With Alter Table

We can also add the DEFAULT constraint to an existing column using the ALTER TABLE command. For example,

SQL Server

ALTER TABLE Colleges
ADD CONSTRAINT country_default
DEFAULT 'US' FOR college_country;

PostgreSQL

ALTER TABLE Colleges
ALTER COLUMN college_code SET DEFAULT 'US';

MySQL

ALTER TABLE Colleges
ALTER college_country SET DEFAULT 'US';

Oracle

ALTER TABLE Colleges
MODIFY college_country DEFAULT 'US';

Here, the default value of college_country column is set to US if NULL is passed during insertion.

Remove Default Constraint

We can remove the default constraint in a column using the DROP clause. For example,

SQL Server, PostgreSQL, Oracle

ALTER TABLE Colleges
ALTER COLUMN college_country DROP DEFAULT;

MySQL

ALTER TABLE Colleges
ALTER college_country DROP DEFAULT;

Here, the SQL command removes the DEFAULT constraint from the college_country column.