In SQL, the DEFAULT
constraint is used to set a default value if we try to insert an empty value into a column.
-- 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'
);
Here, the default value of the college_country column is US.
If we try to store a NULL
value in the college_country column, its value will be US by default.
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 createdcolumn_name
is the name of the column where the constraint is to be implementeddata_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-- 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
.
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.
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.