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,
ALTER TABLE Colleges ADD CONSTRAINT country_default DEFAULT 'US' FOR college_country;
ALTER TABLE Colleges ALTER COLUMN college_code SET DEFAULT 'US';
ALTER TABLE Colleges ALTER college_country SET DEFAULT 'US';
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;
ALTER TABLE Colleges ALTER college_country DROP DEFAULT;
Here, the SQL command removes the
DEFAULT constraint from the college_country column.