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 College (
  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 College (college_id, college_code)
VALUES (1, 'ARP76');

-- Inserts 'UAE' to the college_country column
INSERT INTO College (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 College
ADD CONSTRAINT country_default
DEFAULT 'US' FOR college_country;

PostgreSQL

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

MySQL

ALTER TABLE College
ALTER college_country SET DEFAULT 'US';

Oracle

ALTER TABLE College
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 College
ALTER COLUMN college_country DROP DEFAULT;

MySQL

ALTER TABLE College
ALTER college_country DROP DEFAULT;

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