Relationships in SQL – One-to-One, One-to-Many, Many-to-Many.

Relationships in SQL tables define how tables are connected to one another. Building relationships in tables helps to organize and link data across multiple tables. Creating relationships among tables provides efficient data retrieval and maintenance maintenance.

In this article, we will learn about relationships in SQL, and look at its types like one-to-one, one-to-many, many-to-many, etc. We will provide the syntax and examples to understand relationships and their types.

Relationships in SQL

Relationships in SQL 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.

Type of Relationships in SQL

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

Relationships in SQL

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:

one-to-one relatonship

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:

One-to-many relationship

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:

Many-to-many relationship

4. Many-to-One Relationship

• Definition: Multiple records in table B can be associated with one record in table A.
• Setup: Crate a Foreign key in “Many Table” that references to Primary Key in “One Table”.
• Example: Table Teachers and Courses, many courses can be taught by single teacher.

CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255)
);
CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(255),
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
);

Output:

Many-to-One Relationship

5. 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:

Self-Referencing Relationship

Conclusion

Relationships in SQL are a way to establish connections between multiple tables. This article covers all 5 types of relationships in SQL tables. Explained with examples, this guide easily teaches all types of relationships and provides SQL queries to implement it.