Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

FOREIGN KEY

A FOREIGN KEY is a SQL Server constraint used to create a relationship between two tables. It ensures that a value in one table refers to a valid record in another table.

What is a FOREIGN KEY?

A FOREIGN KEY is a column or combination of columns that refers to the PRIMARY KEY or a UNIQUE key of another table.

It is mainly used to maintain relationships and referential integrity between tables.

Simple Example

A Courses table can have CourseId as its PRIMARY KEY. The Students table can use CourseId as a FOREIGN KEY to connect students with courses.

Why Do We Need a FOREIGN KEY?

  • Creates a relationship between two tables.
  • Maintains referential integrity.
  • Prevents invalid references between related tables.
  • Helps organize related data.
  • Represents relationships in a relational database.

Parent Table and Child Table

A FOREIGN KEY relationship normally consists of a parent table and a child table.

Table Key Role
Courses CourseId PRIMARY KEY
Students CourseId FOREIGN KEY

Step 1: Create Parent Table

First, create the table that contains the referenced key.

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

Step 2: Create Child Table

The Students table can then reference CourseId from the Courses table.

SQL
CREATE TABLE Students
(
    StudentId INT PRIMARY KEY,
    StudentName VARCHAR(100),
    CourseId INT,

    CONSTRAINT FK_Students_Courses
    FOREIGN KEY (CourseId)
    REFERENCES Courses(CourseId)
);

FOREIGN KEY Relationship

SQL Server

                                                    Courses
                                                       |
                                                       | CourseId
                                                       | PRIMARY KEY
                                                       ↓
                                                    Students
                                                       |
                                                       | CourseId
                                                       | FOREIGN KEY
                                                       ↓
                                           References Courses.CourseId

Insert Valid Data

A foreign key value should refer to an existing value in the parent table.

SQL
INSERT INTO Courses
VALUES (1, 'Java');

INSERT INTO Students
VALUES (101, 'Rahul', 1);
Result: The insert is successful because CourseId 1 exists in the Courses table.

Invalid FOREIGN KEY Value

Suppose CourseId 10 does not exist in the Courses table.

SQL
INSERT INTO Students
VALUES (102, 'Priya', 10);
Error: The insert fails because CourseId 10 does not exist in the parent Courses table.

Add FOREIGN KEY to Existing Table

A FOREIGN KEY can also be added after creating the table.

SQL
ALTER TABLE Students
ADD CONSTRAINT FK_Students_Courses
FOREIGN KEY (CourseId)
REFERENCES Courses(CourseId);

ON DELETE CASCADE

ON DELETE CASCADE automatically removes related child records when the corresponding parent record is deleted.

SQL
CREATE TABLE Students
(
    StudentId INT PRIMARY KEY,
    StudentName VARCHAR(100),
    CourseId INT,

    CONSTRAINT FK_Students_Courses
    FOREIGN KEY (CourseId)
    REFERENCES Courses(CourseId)
    ON DELETE CASCADE
);

Example Output

Courses Table
CourseId CourseName
1 Java
2 .NET
Students Table
StudentId StudentName CourseId
101 Rahul 1
102 Priya 2
Important Points
  • FOREIGN KEY creates a relationship between tables.
  • It normally references a PRIMARY KEY or UNIQUE key.
  • Invalid references are prevented.
  • A table can have multiple FOREIGN KEY constraints.
  • ON DELETE CASCADE can be used when appropriate.

Summary

In this section, we learned how FOREIGN KEY creates a relationship between tables, how to create and add a FOREIGN KEY, and how referential integrity prevents invalid references.