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.
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.
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
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.
INSERT INTO Courses
VALUES (1, 'Java');
INSERT INTO Students
VALUES (101, 'Rahul', 1);
Invalid FOREIGN KEY Value
Suppose CourseId 10 does not exist in the Courses table.
INSERT INTO Students
VALUES (102, 'Priya', 10);
Add FOREIGN KEY to Existing Table
A FOREIGN KEY can also be added after creating the table.
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.
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.