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');

The default value of the college_country column is set to US. When we insert a row without specifying a value for the college_country column, it is automatically set to US by default.

However, if we explicitly insert a value such as UAE for the college_country column, the default value is ignored, and the column is set to UAE instead.

If NULL is explicitly provided for the college_country column, the value will be set to NULL, overriding the default value.

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.

Also Read

• SQL Constraints
• SQL CREATE TABLE