?️ MySQL Table Relationships

? Quick Overview

In MySQL, table relationships define how data in one table is connected to data in another table. Relationships improve data organization, reduce duplication, and enforce consistency using foreign keys.

? Key Concepts

• Primary Key (PK) – Uniquely identifies each row in a table
• Foreign Key (FK) – A column that refers to a primary key in another table
• Referential Integrity – Ensures relationships remain valid
• Constraints – Rules applied to columns (FOREIGN KEY, UNIQUE, NOT NULL)
• Normalization – Organizing tables to avoid redundancy

? Syntax / Theory

One-to-One

Each record in Table A relates to one record in Table B.

One-to-Many

One record in Table A can relate to many records in Table B.

Many-to-Many

Records in both tables can be related to multiple records using a junction table.

? Code Example(s)

// Create parent table (Customers)
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50)
);
// Create child table (Orders) with foreign key
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

// Many-to-Many using junction table (students & courses)
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(40)
);
CREATE TABLE courses (
id INT PRIMARY KEY,
title VARCHAR(50)
);
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY(student_id, course_id),
FOREIGN KEY(student_id) REFERENCES students(id),
FOREIGN KEY(course_id) REFERENCES courses(id)
);

? Live Output / Explanation