SQL Server Constraints Guide for DBAs & Developers

Introduction

Introduction to SQL Server Constraints

In SQL Server, constraints act as rules that maintain data integrity and accuracy within a database. They prevent invalid or inconsistent data from being inserted, updated, or deleted, ensuring that the database remains reliable and logically consistent.

For instance, imagine two tables: Employee and Department. To maintain consistent relationships—like ensuring each employee belongs to a valid department—SQL Server uses constraints such as Primary Key, Foreign Key, Unique Key, Not Null, and Check.

Types of Constraints in SQL Server

SQL Server provides several constraint types, each serving a distinct purpose in ensuring data consistency and validity.

1. Primary Key Constraint

Definition and Syntax

A Primary Key uniquely identifies each record in a table.

• Only one primary key is allowed per table.
• It cannot contain NULL values.
• It ensures uniqueness across all rows.

Syntax:

ColumnName DataType(Size) PRIMARY KEY

Example – Column-Level Definition

CREATE TABLE DepartmentManager (
  DepartmentManagerID INT IDENTITY(1,1) PRIMARY KEY,
  EmpID INT,
  DepartmentID INT
);

Table-Level Definition

CREATE TABLE DepartmentManager (
  DepartmentManagerID INT IDENTITY(1,1),
  EmpID INT,
  DepartmentID INT,
  PRIMARY KEY (DepartmentManagerID)
);

Composite Primary Key Example

When a key consists of multiple columns, define it at the table level:

CREATE TABLE DepartmentManager (
  EmpID INT,
  DepartmentID INT,
  PRIMARY KEY (EmpID, DepartmentID)
);

Adding or Dropping Primary Key

ALTER TABLE EmployeeMaster 
ADD CONSTRAINT PK_EmpPrimaryKey PRIMARY KEY (EmpID);

ALTER TABLE EmployeeMaster 
DROP CONSTRAINT PK_EmpPrimaryKey;

2. Foreign Key Constraint

Purpose and Functionality

A Foreign Key enforces referential integrity by linking one table’s column to another table’s Primary Key. It ensures that a value in the child table must exist in the parent table.

Syntax:

ColumnName DataType REFERENCES TableName(ColumnName)

Example

CREATE TABLE DepartmentManager (
  DepartmentManagerID INT IDENTITY(1,1) PRIMARY KEY,
  EmpID INT REFERENCES Employee(EmpID),
  DepartmentID INT
);

Adding and Dropping Foreign Key

ALTER TABLE DepartmentManager 
ADD CONSTRAINT FK_DepartmentID FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID);

ALTER TABLE DepartmentManager 
DROP CONSTRAINT FK_DepartmentID;

Self-Referencing Foreign Key Example

A table can reference itself:

CREATE TABLE Employee (
  EmpID INT IDENTITY(1,1) PRIMARY KEY,
  EmpName NVARCHAR(100),
  ManagerID INT REFERENCES Employee(EmpID)
);

3. Unique Key Constraint

Definition

The Unique Key constraint ensures that all non-null values in a column are unique.
Unlike a primary key, a unique key can allow multiple NULLs.

Syntax:

ColumnName DataType UNIQUE

Example

CREATE TABLE DepartmentManager (
  DepartmentManagerID INT IDENTITY(1,1),
  EmpID INT UNIQUE,
  DepartmentID INT
);

Composite Unique Key Example

CREATE TABLE DepartmentManager (
  EmpID INT,
  DepartmentID INT,
  UNIQUE (EmpID, DepartmentID)
);

Add or Drop Unique Key

ALTER TABLE EmployeeSalaryMaster 
ADD CONSTRAINT UQ_EmpID UNIQUE (EmpID);

ALTER TABLE EmployeeSalaryMaster 
DROP CONSTRAINT UQ_EmpID;

4. Not Null Constraint

Concept

A Not Null constraint ensures that a column cannot contain NULL values. It is always defined at the column level.

Syntax:

ColumnName DataType NOT NULL

Example

CREATE TABLE DepartmentManager (
  DepartmentManagerID INT IDENTITY(1,1),
  EmpID INT NOT NULL,
  DepartmentID INT
);

5. Check Constraint

Definition

A Check constraint allows you to define custom rules for acceptable column values.

Syntax (Column Level):

ColumnName DataType CHECK (Logical_Expression)

Example:

CREATE TABLE EmployeeSalaryMaster (
  EmpSalaryID INT IDENTITY(1,1),
  EmpID INT,
  EmpSalary NUMERIC(10,2) CHECK (EmpSalary > 5000) NOT NULL,
  SalaryDt DATETIME
);

Table-Level Example:

CREATE TABLE EmployeeSalaryMaster (
  EmpSalaryID INT IDENTITY(1,1),
  EmpID INT,
  EmpSalary NUMERIC(10,2) NOT NULL,
  SalaryDt DATETIME,
  CHECK (EmpSalary > 5000)
);

Best Practices for Using Constraints

• Always define Primary Keys for unique identification.
• Use Foreign Keys to maintain referential integrity.
• Combine Check Constraints with Not Null for strong validation.
• Avoid overusing Unique Keys unless necessary—they add indexing overhead.

Common Mistakes and How to Avoid Them

• Defining composite primary keys unnecessarily when surrogate keys (IDENTITY) suffice.
• Forgetting to drop constraints before modifying structure.
• Creating redundant unique constraints on indexed columns.

FAQs on SQL Server Constraints

Q1. Can a table have multiple primary keys?
No. Each table can only have one primary key, but it can consist of multiple columns.

Q2. Can a unique key accept NULL values?
Yes, unlike primary keys, unique keys can accept multiple NULLs.

Q3. What happens if a foreign key is violated?
The system throws an error preventing insertion or deletion that breaks referential integrity.

Q4. Can we disable a constraint temporarily?
Yes, using ALTER TABLE … NOCHECK CONSTRAINT temporarily disables constraint enforcement.

Q5. What’s the difference between CHECK and FOREIGN KEY?
CHECK validates column-level rules, while FOREIGN KEY ensures referential integrity between tables.

Conclusion

SQL Server constraints are the foundation of data reliability, accuracy, and relational integrity. Whether defining a primary key to uniquely identify records or a foreign key to maintain relationships, constraints safeguard the quality of data in every operation. Mastering them is essential for any SQL professional striving for performance and consistency.