SQL DEFAULT Constraint

-- set default value of college_country column to 'US'
CREATE TABLE College (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20),
  college_country VARCHAR(20) DEFAULT 'US'
);

DEFAULT Constraint Syntax

The syntax of the SQL DEFAULT constraint is:

CREATE TABLE table_name (
  column_name data_type DEFAULT default_value
);

Here,

• table_name is the name of the table to be created
• column_name is the name of the column where the constraint is to be implemented
• data_type is the data type of the column such as INT, VARCHAR, etc.
• default_value is the value that the inserted empty values are replaced with

Example: SQL DEFAULT Constraint

-- don't add any value to college_country column
-- thus default value 'US ' is inserted to the column
INSERT INTO Colleges (college_id, college_code)
VALUES (1, 'ARP76');

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

Here, the default value of the college_country column is set to US. So, when we try to insert a NULL value to the college_country column, it is replaced with US by default.

But when we set college_country to UAE, the default value is ignored and the value of the column is set as 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 the college_country column is set to US if NULL is passed during insertion.

Remove Default Constraint

We can use the DROP clause to remove the DEFAULT constraint in a column. 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.

Recommended Reading: SQL Constraints