A Default Constraint in SQL Server is used to automatically assign a default value to a column when no value is provided during an INSERT operation.
Why do we use DEFAULT constraints?
- Ensure data consistency when users skip inserting values.
- Helps avoid NULL values unless intentionally allowed.
- Often used for bit fields (IsActive), timestamps (GETDATE()), or static defaults (‘Pending’).
Steps to Create a DEFAULT Constraint in SSMS
- Open SQL Server Management Studio (SSMS).
- Right-click on the desired table e.g. Employee and select Design.
- Select the column e.g. IsActive on which you want to apply the Default Constraint.
- Set the Default value or Binding to 1
Testing the Default Constraint
INSERT INTO dbo.Employee
(EmpID, EmpName, Gender, Department, Salary, JoiningDate, ManagerID)
VALUES
(101, 'Shaun Abbot', 'Male', 'IT', 88000, '2020-03-15', NULL),
(102, 'Stefee Taylor', 'Female', 'HR', 88000, '2020-07-01', 105);
GO
SELECT * FROM DBO.Employee
GO
You can create Default Constraint using T-SQL:
ALTER TABLE Employee
ADD CONSTRAINT Default_IsActive
DEFAULT 1 FOR [IsActive];