Relational Database Concepts: Keys & Table Relationships 📌
Relational Database Concepts: Keys & Table Relationships 📌
A relational database organizes data into tables with structured relationships. Keys help maintain data integrity and create connections between tables.
1️⃣ Primary Key (PRIMARY KEY)
A Primary Key is a unique identifier for each row in a table.
✅ Characteristics of a Primary Key:
✔ Unique for each record.
✔ Cannot be NULL (ensures every row has an identifier).
✔ There can only be one primary key per table.
✅ Example: Creating a Table with a Primary Key
CREATE TABLE students (
student_id INT PRIMARY KEY, -- Unique identifier
name VARCHAR(50),
age INT
);
✅ Example: Auto-Incrementing Primary Key
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);
AUTO_INCREMENT automatically generates a unique ID for new records.
2️⃣ Foreign Key (FOREIGN KEY)
A Foreign Key establishes a relationship between two tables by referencing a Primary Key in another table.
✅ Characteristics of a Foreign Key:
✔ Links two tables together.
✔ Ensures referential integrity (prevents invalid data).
✔ Can have duplicate values (unless UNIQUE is specified).
✅ Example: Creating a Foreign Key Relationship
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
student_id in enrollments refers to student_id in students.
course_id in enrollments refers to course_id in courses.
This prevents invalid enrollments (e.g., a student who doesn’t exist).
3️⃣ Understanding Relationships Between Tables
Tables in a relational database can have different types of relationships.
Relationship Type Description Example
One-to-One (1:1) Each record in Table A has exactly one matching record in Table B. A person has one passport.
One-to-Many (1:M) Each record in Table A can relate to multiple records in Table B. A teacher teaches many students.
Many-to-Many (M:M) Records in Table A can relate to multiple records in Table B, and vice versa. A student enrolls in many courses, and a course has many students.
✅ One-to-One Example
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE user_profiles (
profile_id INT PRIMARY KEY,
user_id INT UNIQUE,
bio TEXT,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
Each user has one profile.
user_id in user_profiles is UNIQUE, ensuring one-to-one mapping.
✅ One-to-Many Example
CREATE TABLE teachers (
teacher_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
A teacher can have many students.
teacher_id in students references teacher_id in teachers.
✅ Many-to-Many Example (Using a Join Table)
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
A student can enroll in many courses, and each course can have many students.
The enrollments table acts as a bridge between students and courses.
🎯 Summary
✔ Primary Key (PRIMARY KEY) – Uniquely identifies records.
✔ Foreign Key (FOREIGN KEY) – Links tables together.
✔ One-to-One (1:1) – Each record in one table matches exactly one in another.
✔ One-to-Many (1:M) – One record in a table relates to many in another.
✔ Many-to-Many (M:M) – Many records in one table relate to many in another using a join table.
Date: 2025-03-29 00:00:00.000000