The DEFAULT constraint is used to provides a default value to a column. The default value will be added to all new records when the "INSERT INTO" statement does not provide any specific value, including NULL.

(1) Add a default constraint when we create a table

Syntax: 

CREATE TABLE TableName (
	ColumnName DEFAULT DefaultValue 
);
Example: 

CREATE TABLE TblStudent
(
	SRNo VARCHAR(15),
	StudentName VARCHAR(50),
	FatherName VARCHAR(50),
	CourseCode VARCHAR(15),
	Country VARCHAR(50) DEFAULT 'INDIA'
)
In the above created table when we will not provide any value for Country Column in "INSERT INTO" statement then it will take "INDIA" as the default Country. 

INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode)
VALUES ('015II001','Karan','Shyam','BCA')
INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode)
VALUES ('015II002','Hari Om','Om Prakash','MBA')
Output: 

SELECT * FROM TblStudent

(2) Add a default constraint to an existing column

Syntax:

ALTER TABLE TableName 
ADD CONSTRAINT ConstraintName  
DEFAULT DefaultValue FOR ExistingColumnName
Example: In the given example we will add default constraint "DF_TblStudent_CourseCode" to provide default course.

ALTER TABLE TblStudent
ADD CONSTRAINT DF_TblStudent_CourseCode
DEFAULT 'BCA' FOR CourseCode
So in this example when we will not provide any value for CourseCode in "INSERT INTO" statement then default constraint "DF_TblStudent_CourseCode" will provide default value "BCA".

(3) Add a default constraint when add a new column to an existing table

Syntax:

ALTER TABLE TblStudent 
ADD ColumnName  DataType [NULL | NOT NULL]
CONSTRAINT ConstraintName  DEFAULT DefaultValue
Example: In the given example we will add default constraint "DF_TblStudent_Gender" to provide default Gender.

ALTER TABLE TblStudent 
ADD Gender  VARCHAR(10) NOT NULL
CONSTRAINT DF_TblStudent_Gender DEFAULT 'Male'
So in this example when we will not provide any value for Gender in "INSERT INTO" statement then default constraint "DF_TblStudent_Gender" will provide default value "Male".

(4) DROP a Constraint

Syntax:

ALTER TABLE TableName 
DROP CONSTRAINT ConstraintName
Example: 

ALTER TABLE TblStudent
DROP CONSTRAINT DF_TblStudent_CourseCode
Above SQL statement will delete the defauld constraint "DF_TblStudent_CourseCode" from TblStudent table