Keys and Relationships
SQL Basics Course
Topic 4: Keys and Relationships
Learn how databases uniquely identify records and connect tables together.
Learning Objectives
- Understand database keys
- Learn Primary Keys
- Learn Foreign Keys
- Understand Candidate and Composite Keys
- Learn table relationships
Why Keys Are Needed
Keys help uniquely identify records and maintain data integrity inside a database.
| Student_ID | Name | City |
|---|---|---|
| 1 | Rahul | Delhi |
| 2 | Amit | Mumbai |
Student_ID uniquely identifies each student.
Primary Key
A Primary Key uniquely identifies every record in a table.
- Must be unique
- Cannot be NULL
- Only one primary key per table
Students Table → Student_ID (Primary Key)
Candidate Key
A Candidate Key is any column that can uniquely identify a record.
| Student_ID | Name | |
|---|---|---|
| 1 | rahul@email.com | Rahul |
Both Student_ID and Email could be candidate keys.
Composite Key
A Composite Key is formed using two or more columns together.
| Student_ID | Course_ID | Grade |
|---|---|---|
| 1 | 101 | A |
Student_ID + Course_ID can form a composite key.
Foreign Key
A Foreign Key creates a relationship between two tables.
| Students | Courses | |
|---|---|---|
| Student_ID | → | Course_ID |
Foreign Keys connect related data stored in different tables.
One-to-One Relationship
Person ↔ Passport
One Person = One Passport
Each record in one table corresponds to one record in another table.
One-to-Many Relationship
Department → Employees
One Department = Many Employees
This is the most common relationship type in databases.
Many-to-Many Relationship
Students ↔ Courses
One Student = Many Courses
One Course = Many Students
A junction table is usually required to implement this relationship.
Relationship Example
| Students | Enrollments | Courses |
|---|---|---|
| Student_ID | Student_ID | Course_ID |
| Name | Course_ID | Course_Name |
The Enrollment table acts as a bridge between Students and Courses.
Best Practices
- Always define primary keys
- Use meaningful foreign keys
- Avoid duplicate records
- Maintain referential integrity
- Choose keys carefully
Practice Exercises
- Define Primary Key.
- Define Foreign Key.
- Explain Candidate Key.
- What is a Composite Key?
- Describe a Many-to-Many relationship.
Quiz
1. Can a Primary Key contain duplicate values?
2. Which key links two tables?
3. Can a Candidate Key become a Primary Key?
4. Which relationship requires a junction table?
5. Can a Primary Key be NULL?
Summary
- Primary Keys uniquely identify records.
- Foreign Keys connect tables.
- Candidate Keys are possible Primary Keys.
- Composite Keys use multiple columns.
- Relationships connect data across tables.