In relational database design, understanding how tables relate to each other is critical. Whether you're building a social media platform, e-commerce system, or blog site, you’ll encounter different types of relationships. In this article, I’ll walk through the four main types of relationships in SQL:

• One-to-One (1:1)

• One-to-Many (1:N)

• Many-to-One (N:1)

• Many-to-Many (M:N)

Each will be explained with clear examples and SQL schema.

Relationship

1. One-to-One (1:1) Relationship

📌 Definition: A row in table A is linked to exactly one row in table B, and vice versa.

Example: users and user_profiles

user_id is unique in user_profiles, ensuring only one profile per user.

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

CREATE TABLE user_profiles (
    profile_id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(user_id),
    bio TEXT
);

🔁 2. One-to-Many (1:N) Relationship

📌 Definition: A row in table A can be related to multiple rows in table B, but each row in B relates to only one row in A.

Example: customers and orders

One customer can have many orders.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    product_name VARCHAR(100)
);

🔄 3. Many-to-One (N:1) Relationship

📌 Definition: The reverse of One-to-Many. Many rows in table A are linked to one row in table B.

✅ It's the same concept as One-to-Many — just viewed from the other side.

Example: orders → customers

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    product_name VARCHAR(100)
);
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

4. Many-to-Many (M:N) Relationship

📌 Definition: Many rows in table A relate to many rows in table B. This requires a junction table.

Example: students and courses

A student can enroll in many courses, and a course can have many students.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100)
);

-- Junction table
CREATE TABLE student_courses (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

📌 Summary Table

🗝️ Understanding Primary Key and Foreign Key in SQL

Before we explore relationships in SQL (1:1, 1:N, M:N), it’s essential to understand the Primary Key and Foreign Key constraints — the foundation of relational integrity.

🔑 What is a Primary Key?

• A Primary Key uniquely identifies each record in a table.

• It cannot contain NULL values and must be unique.

• Every table should ideally have one.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

What is a Foreign Key?

• A Foreign Key is a field in one table that references the primary key in another table.

• It is used to establish relationships between tables.

• Maintains referential integrity (i.e., the referenced row must exist).

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES users(user_id),
    product_name VARCHAR(100)
);
 -- customer_id in orders is a foreign key referring to user_id in users.
-- It ensures you can’t insert an order for a user who doesn’t exist.

--  You can also declare foreign keys like this:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES users(user_id)
);