Relationships and Keys in PostgreSQL

One-to-One, One-to-Many, and Many-to-Many Relationships

In relational databases like PostgreSQL, relationships describe how tables are linked to each other. Here are the three main types of relationships:

1. One-to-One Relationship

A one-to-one relationship occurs when each row in one table corresponds to exactly one row in another table.

Example

Use Case: Storing user profiles and authentication data separately.

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

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Each user has exactly one profile.

2. One-to-Many Relationship

A one-to-many relationship occurs when a row in one table can relate to multiple rows in another table.

Example

Use Case: Authors and their books.

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author_id INT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

One author can write many books, but each book is written by one author.

3. Many-to-Many Relationship

A many-to-many relationship occurs when rows in one table can relate to multiple rows in another table, and vice versa. This is typically implemented using a junction table.

Example

Use Case: Students and courses.

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

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

Primary Key and Foreign Key in SQL

Primary Key

A primary key is a column (or a combination of columns) that uniquely identifies each row in a table.

Features

Unique: No two rows can have the same value for the primary key column(s).
Non-Nullable: A primary key cannot have NULL values.
There can only be one primary key per table.

Example

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

The id column is the primary key and uniquely identifies each employee.

Foreign Key

A foreign key is a column (or a set of columns) in one table that establishes a relationship with the primary key in another table.

Features

Ensures referential integrity between two tables.
A foreign key in one table refers to a primary key in another table.

Example

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

The customer_id in the orders table is a foreign key referencing the id in the customers table.

Summary

Term	Definition
One-to-One	Each row in one table corresponds to exactly one row in another.
One-to-Many	A row in one table relates to multiple rows in another.
Many-to-Many	Rows in one table relate to multiple rows in another, using a junction table.
Primary Key	Uniquely identifies each row in a table.
Foreign Key	References a primary key in another table to establish a relationship.

These concepts are foundational for designing relational databases with proper normalization and data integrity.

Interview angle

“Primary key: natural or surrogate?” - surrogate by default, because natural keys change and then cascade through every foreign key. Keep the natural key as a unique constraint so the invariant is still enforced.
“UUID or auto-increment?” - auto-increment is compact and index-friendly; random UUIDs fragment the index, which is worse in MySQL where the table is clustered on the primary key. UUIDv7 is time-ordered and gets you distributed generation without the fragmentation.
“What do foreign keys actually give you?” - enforced referential integrity at the database level, which application code cannot guarantee under concurrency. They cost a check on write and require care about the ON DELETE behaviour.
“When would you denormalise?” - when a join is provably the bottleneck and the duplicated data changes rarely. Normalise first, denormalise with measurements, and be explicit about how the copies stay consistent.