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.