SQL Constraints – PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL Explained

Microsoft SQL Server Tutorial Series: Beginner to Expert

Part 6: SQL Constraints – PK, FK, UNIQUE, NOT NULL

---

Welcome back! In this part of our SQL Server series, we’ll explore SQL Constraints, the rules that help ensure your database is accurate, consistent, and reliable.

---

📚 What You’ll Learn

• What SQL Constraints are and why they matter
• How to use PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL
• How to add constraints while creating or altering tables
• Real-world examples using Students and Courses tables

---

🔐 What Are SQL Constraints?

Constraints are rules applied to table columns to control the type of data that can be stored. They help enforce data integrity and prevent bad or duplicate data from being inserted.

---

🧱 1. PRIMARY KEY Constraint

The PRIMARY KEY uniquely identifies each row in a table. It must be:

• Unique (no duplicates)
• Not NULL

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

💡 You can only have one primary key per table, but it can consist of one or more columns (composite key).

---

🔗 2. FOREIGN KEY Constraint

The FOREIGN KEY establishes a link between two tables. It ensures that the value in one table matches a value in another table.

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

💡 If a referenced value doesn’t exist in the parent table, SQL Server will throw an error — this maintains referential integrity.

---

🔁 3. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Name VARCHAR(50)
);

💡 Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, but each column must still allow only unique values.

---

🚫 4. NOT NULL Constraint

NOT NULL means a column must have a value — it can’t be left empty.

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL
);

💡 Use this for fields like names, dates, or emails that should never be blank.

---

🧾 SQL Cheat Sheet

-- PRIMARY KEY
CREATE TABLE Example (
    ID INT PRIMARY KEY
);

-- FOREIGN KEY
FOREIGN KEY (ColumnName) REFERENCES OtherTable(OtherColumn)

-- UNIQUE
Email VARCHAR(100) UNIQUE

-- NOT NULL
Name VARCHAR(50) NOT NULL

---

🌍 Real-World Example

In a school system:

• StudentID in Students is a PRIMARY KEY
• CourseID in Enrollments is a FOREIGN KEY
• Email in Teachers must be UNIQUE
• CourseName in Courses should be NOT NULL

---

📋 Quick Comparison Table

Constraint	Purpose	Allows NULL?	Allows Duplicates?
PRIMARY KEY	Uniquely identifies each row	No	No
FOREIGN KEY	Creates link between tables	Yes	Yes
UNIQUE	Ensures all values are different	Yes	No
NOT NULL	Requires a value in the column	No	Yes

---

✅ Best Practices

• Always use PRIMARY KEY for every table.
• Use FOREIGN KEYS to define relationships.
• Combine UNIQUE + NOT NULL to enforce data quality.
• Name your constraints explicitly for clarity (e.g., CONSTRAINT FK_StudentID).

---

🧠 Summary

Today, you learned how constraints help maintain the accuracy and integrity of your data:

• PRIMARY KEY – Uniquely identifies each record
• FOREIGN KEY – Creates table relationships
• UNIQUE – Ensures no duplicates
• NOT NULL – Requires non-empty values

Constraints are essential in professional database design — now you know how to use them confidently! 🎉

---

🔗 Previous & Next Posts

• ← Part 5: INSERT, UPDATE, DELETE Commands
• Part 7: Working with Date, String, and Numeric Types →

Questions or feedback? Leave a comment below and share your progress! 🙌

---

← Previous Next →

Understanding Primary and Foreign Keys in Databases (With Examples for Beginners)

 

← Back to Home

---

🔷 Part 4: Primary Keys and Foreign Keys – Building Relationships in Databases

---

📍 Introduction

So far, we’ve learned how data is stored in tables and how we use SQL to interact with it. But what happens when your database has more than one table?

That’s where relationships come in — and they’re built using keys.

Keys help connect tables, maintain data integrity, and allow complex queries across multiple data sets. Today, we’ll explore Primary Keys and Foreign Keys, the foundation of relational database design.

---

🔑 What is a Primary Key?

A Primary Key is a unique identifier for each record in a table. No two rows can have the same primary key, and it can’t be left empty (NULL).

🔍 Example – Students Table:

| StudentID | Name  | Course     |
|-----------|-------|------------|
| 1         | Aisha | Math       |
| 2         | Ravi  | Science    |
| 3         | Sara  | English    |

Here, StudentID is the Primary Key — each student has a unique ID.

📘 Think of it like a roll number in a classroom — no two students have the same roll number.

---

🔗 What is a Foreign Key?

A Foreign Key is a column in one table that refers to the primary key in another table. It creates a link between two related tables.

---

🧩 Example: Students & Results Tables

🧾 Students Table

| StudentID | Name  |
|-----------|-------|
| 1         | Aisha |
| 2         | Ravi  |

🧾 Results Table

| ResultID | StudentID | Subject  | Marks |
|----------|-----------|----------|-------|
| 101      | 1         | Math     | 85    |
| 102      | 2         | Science  | 90    |

In this case:

• StudentID is the Primary Key in the Students table.

• StudentID in the Results table is a Foreign Key — it refers to the Students table.

💡 This relationship ensures that every result belongs to a valid student.

---

🔁 Why Use Keys and Relationships?

• ✅ Data Integrity: Prevents orphaned or mismatched records

• ✅ Less Redundancy: No need to repeat data across tables

• ✅ Scalability: Makes your database easier to maintain as it grows

• ✅ Real-Life Modeling: Mimics real-world relationships (students have results, customers place orders, etc.)

---

🔧 SQL Example: Defining Primary and Foreign Keys

-- Create Students Table
CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);

-- Create Results Table
CREATE TABLE Results (
  ResultID INT PRIMARY KEY,
  StudentID INT,
  Subject VARCHAR(50),
  Marks INT,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

---

📚 Real-Life Analogy

Imagine:

• Primary Key = National ID Number

• Foreign Key = Form asking for your National ID

You can’t submit the form unless your ID is valid — just like foreign keys rely on real, matching primary keys.

---

🧠 Recap

• Primary Key: Uniquely identifies a row in a table (e.g., StudentID)

• Foreign Key: Connects a row to another table using a reference

• These keys help create relationships between tables and ensure the accuracy and reliability of your data.

---

✅ What’s Next?

In Part 5, we’ll explore NoSQL databases — a modern alternative to relational databases that works better for unstructured data, large-scale applications, and real-time needs.

← Previous Practice Quiz →