Constraints Introduction
SQL Server constraints are rules applied to table columns to maintain data accuracy, consistency and integrity. Constraints help control what type of data can be inserted or stored in a table.
What are Constraints?
A constraint is a rule that is applied to a column or table to control the data that can be stored.
Constraints help ensure that invalid or unwanted data is not inserted into the database.
Simple Example
If every student must have a unique StudentId, we can use a PRIMARY KEY constraint to enforce that rule.
Why Do We Need Constraints?
Constraints help maintain the quality and integrity of data stored in database tables.
- Prevent invalid data.
- Maintain data integrity.
- Prevent duplicate values where required.
- Establish relationships between tables.
- Apply rules to column values.
- Provide consistency across the database.
Types of SQL Server Constraints
| Constraint | Purpose | Example |
|---|---|---|
| PRIMARY KEY | Uniquely identifies each record in a table. | StudentId |
| FOREIGN KEY | Creates a relationship between tables. | CourseId |
| UNIQUE | Prevents duplicate values in a column. | |
| NOT NULL | Prevents NULL values in a column. | StudentName |
| DEFAULT | Provides a default value when no value is supplied. | Status |
| CHECK | Restricts values according to a condition. | Age >= 18 |
Constraint Structure
Constraints can be applied while creating a table or added later to an existing table.
Database
↓
Table
↓
Columns
↓
Constraints
↓
Data Integrity
Constraints Example
The following example uses multiple constraints while creating a Students table.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(150) UNIQUE,
Age INT CHECK (Age >= 18),
Status VARCHAR(20) DEFAULT 'Active'
);
Example Table Structure
| Column | Data Type | Constraint |
|---|---|---|
| StudentId | INT | PRIMARY KEY |
| StudentName | VARCHAR(100) | NOT NULL |
| VARCHAR(150) | UNIQUE | |
| Age | INT | CHECK |
| Status | VARCHAR(20) | DEFAULT |
Constraints and Data Integrity
Data integrity means maintaining accurate and consistent data in the database.
Valid Data
↓
Constraints
↓
Database Rules
↓
Accurate & Consistent Data
Important Points
- Constraints enforce rules on table data.
- Different constraints solve different data integrity requirements.
- Constraints can be defined during table creation.
- Constraints can also be added to existing tables.
Summary
In this section, we learned what SQL Server constraints are, why they are required and the different types of constraints used to maintain data integrity.