Relationships in MySQL

Relationships in MySQL refer to the associations or connections between tables in a relational database. These relationships are established using foreign keys, which are columns in a table that refer to the primary key in another table. Relationships help organize and structure data, allowing for efficient data retrieval and maintaining data integrity.

There are different types of relationships: one-to-one, one-to-many, many-to-many, and self-referencing

1. One-to-One Relationship:

Definition: Each record in Table A is associated with one and only one record in Table B, and vice versa.

Setup: Include a foreign key in one of the tables that references the primary key of the other table.

For example: Tables users and user_profiles, where each user has a single corresponding profile.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT UNIQUE,
    profile_data VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Output:

2. One-to-Many Relationship:

Definition: Each record in Table A can be associated with multiple records in Table B, but each record in Table B is associated with only one record in Table A.

Setup: Include a foreign key in the “many” side table (Table B) that references the primary key of the “one” side table (Table A).

For example: Tables departments and employees, where each department can have multiple employees, but each employee belongs to one department.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Output:

3. Many-to-Many Relationship:

Definition: Each record in Table A can be associated with multiple records in Table B, and vice versa.

Setup: Create an intermediate table (also known as a junction or linking table) that contains foreign keys referencing both related tables.

For example: Tables students and courses, where each student can enroll in multiple courses, and each course can have multiple students.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE student_courses (
    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)
);

Output:

4. Self-Referencing Relationship:

Definition: A table has a foreign key that references its primary key.

Setup: Include a foreign key column in the same table that references its primary key.

For example A table employees with a column manager_id referencing the same table’s employee_id.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Output:

MySQL is a widely used relational database management system (RDBMS) that provides a robust and scalable platform for managing and organizing data. MySQL is an open-source software developed by Oracle Corporation, that provides features for creating, modifying, and querying databases. It utilizes the Structured Query Language (SQL) to interact with databases, making it a popular choice for web applications and various software systems. MySQL’s versatility, reliability, and ease of use make it a preferred solution for developers and organizations seeking efficient data management capabilities.

In this article, you will learn about How to select the last records in a one-to-many relationship using MySQL join by using examples.