Constraints
Constraints are rules the database enforces on every change, no matter which application or person makes it. They are your last line of defence for data quality.
On this page · 5 min · Advanced
On this page
- In simple words
- Learning objectives
- Explanation
- Syntax
- Code example
- Step by step
- Real-world use
- Common mistakes
- Best practices
- Remember this
- Practice exercise
- Mini quiz
- Related topics
- My notes
- Reading time
- 5 min
- Difficulty
- Advanced
- Updated
- Oct 27, 2026
Related tutorials
In simple words#
Constraints are the office rules printed on each register column: “required”, “must be unique”, “age must be 5 to 18”. The database refuses entries that break them.
- NOT NULL / UNIQUE
- Required / no duplicates.
- CHECK
- A custom rule (Price > 0).
- DEFAULT
- Value used when none is given.
Learning objectives#
- Apply NOT NULL, UNIQUE, CHECK and DEFAULT
- Name constraints clearly
- Add constraints to existing tables
- Read constraint violation errors
Explanation#
NOT NULL requires a value. UNIQUE prevents duplicates. CHECK enforces a condition such as Salary > 0. DEFAULT supplies a value when none is given.
PRIMARY KEY combines NOT NULL and UNIQUE to identify rows; FOREIGN KEY keeps relationships valid.
Name constraints explicitly (CK_Employees_Salary) so error messages and deployment scripts are readable.
Syntax#
Email VARCHAR(254) NOT NULL CONSTRAINT UQ_Employees_Email UNIQUE,
Salary DECIMAL(12,2) CONSTRAINT CK_Employees_Salary CHECK (Salary > 0)
Code example: Rules the database enforces#
CREATE TABLE Employees (
EmployeeId INT IDENTITY CONSTRAINT PK_Employees PRIMARY KEY,
Email VARCHAR(254) NOT NULL CONSTRAINT UQ_Employees_Email UNIQUE,
Salary DECIMAL(12,2) NOT NULL CONSTRAINT CK_Employees_Salary CHECK (Salary > 0),
IsActive BIT NOT NULL CONSTRAINT DF_Employees_IsActive DEFAULT 1
);
INSERT INTO Employees (Email, Salary) VALUES ('asha@example.com', 50000); -- OK
INSERT INTO Employees (Email, Salary) VALUES ('ravi@example.com', -10); -- fails
(1 row affected) Msg 547, Level 16, State 0 The INSERT statement conflicted with the CHECK constraint "CK_Employees_Salary". The conflict occurred in database "ShopDB", table "dbo.Employees", column 'Salary'.
This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.
Step-by-step explanation#
- The first insert satisfies every rule; IsActive defaults to 1.
- The second violates the CHECK constraint, so SQL Server rejects it with error 547.
- Because the constraint is named, the error tells you exactly which rule failed.
Real-world use case#
Even if a bug in the web app tries to save a negative price, a CHECK constraint stops bad data from ever being stored.
Common mistakes#
Scripts, imports and other apps bypass application rules. Constraints protect the data itself.
Auto-generated names like CK__Employe__Salar__3B75D760 differ per database, making scripts fragile.
Best practices#
- Name every constraint
- Use NOT NULL by default
- Express simple business rules as CHECK constraints
- Validate in the app for friendly messages and in the database for integrity
Remember this#
NOT NULL, UNIQUE, CHECK, DEFAULT, PK, FK.
Constraints protect data even from buggy apps.
Name your constraints for clear errors.
Practice exercise#
Your task: Add a CHECK constraint to Orders allowing only Pending, Delivered or Cancelled statuses.
Show solution
ALTER TABLE Orders ADD CONSTRAINT CK_Orders_Status CHECK (Status IN ('Pending', 'Delivered', 'Cancelled'));
Mini quiz#
Which constraint supplies a value when none is provided?
Want more? Practise all SQL Server MCQs · SQL Server interview questions
My notes#
Saved in this browser.