What are Constraints?
Constraints enforce rules at the table level to guarantee data integrity and consistency. They are defined when creating or altering a table and can be:
- PRIMARY KEY – uniquely identifies each row.
- FOREIGN KEY – enforces referential integrity between tables.
- UNIQUE – guarantees distinct values across one or more columns.
- CHECK – validates that column values meet a Boolean expression.
- DEFAULT – provides a default value for a column when none is supplied.
Common Constraint Syntax
CREATE TABLE dbo.Employee (
EmployeeID INT PRIMARY KEY,
DepartmentID INT,
Salary MONEY CHECK (Salary >= 0),
Email NVARCHAR(255) UNIQUE,
CONSTRAINT FK_Employee_Department
FOREIGN KEY (DepartmentID) REFERENCES dbo.Department(DepartmentID)
);Constraint Types
| Constraint | Purpose | Typical Syntax |
|---|---|---|
| PRIMARY KEY | Unique identifier for each row; cannot be NULL | PRIMARY KEY (column1 [, column2]) |
| FOREIGN KEY | Ensures a value exists in the referenced table | FOREIGN KEY (col) REFERENCES OtherTable(OtherCol) |
| UNIQUE | All values must be distinct | UNIQUE (col1 [, col2]) |
| CHECK | Validates column value against an expression | CHECK (col > 0) |
| DEFAULT | Provides a fallback value | DEFAULT (value) |