One of the fundamental concepts in PostgreSQL, and relational databases in general, is relations. Understanding relations is crucial for effectively modeling and managing data in PostgreSQL. In this blog post, we'll explore what relations are, their types, and provide practical examples to illustrate their usage.
What are Relations?
In the context of databases, a relation is simply a table that stores related data. Each row in the table represents a unique record, and each column corresponds to a specific attribute of that record. For instance, consider a simple relation called employees, where each row represents an employee with attributes like employee_id, name, designation, and department. Relations help maintain the integrity of data by enforcing constraints and allowing us to efficiently query and retrieve information.
Types of Relations
There are three primary types of relations based on how data is related between tables:
1. One-to-Many (1:N)
In a one-to-many relationship, one record in the first table (parent table) can be associated with multiple records in the second table (child table). However, each record in the child table can be linked to only one record in the parent table.
Example:
Consider two tables, orders and order_items. Each order can have multiple items, but each item belongs to only one order.
-- Table: orders
| order_id | customer_name | order_date |
| -------- | ------------- | ----------- |
| 1 | John Smith | 2023-05-15 |
| 2 | Jane Doe | 2023-06-02 |
-- Table: order_items
| item_id | order_id | product_name | quantity |
| ------- | -------- | ------------ | -------- |
| 101 | 1 | Laptop | 2 |
| 102 | 1 | Smartphone | 3 |
| 103 | 2 | Headphones | 1 |
2. Many-to-One (N:1)
A many-to-one relationship is the inverse of a one-to-many relationship. In this case, multiple records in the first table can be associated with a single record in the second table.
Example:
Continuing from the previous example, the relationship between order_items and orders is many-to-one.
3. Many-to-Many (N:N)
A many-to-many relationship involves multiple records in both tables being associated with each other.
Example:
Consider two tables, students and courses. A student can enroll in multiple courses, and each course can have multiple students.
-- Table: students
| student_id | name | age |
| ---------- | --------- | --- |
| 201 | Alice | 22 |
| 202 | Bob | 23 |
-- Table: courses
| course_id | course_name |
| --------- | ------------ |
| 301 | Math |
| 302 | Physics |
-- Table: enrollments (linking table)
| enrollment_id | student_id | course_id |
| ------------- | ---------- | --------- |
| 501 | 201 | 301 |
| 502 | 201 | 302 |
| 503 | 202 | 301 |
Creating Relations in PostgreSQL
In PostgreSQL, we can establish relationships between tables using foreign keys. This ensures referential integrity and maintains the connections between related data. For instance, let's add a foreign key constraint to the order_items table that references the orders table.
--Use this way when the Column <order_id> already exists/created
ALTER TABLE order_items
ADD FOREIGN KEY (order_id) REFERENCES orders(order_id);
-- Use this way when the Column <order_id> is not exist
ALTER TABLE order_items
ADD COLUMN order_id INT REFERENCES orders(order_id);
Or during the creation:
-- Table: orders
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
order_date DATE
);
-- Table: order_items
CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(order_id),
product_name VARCHAR(100),
quantity INTEGER
);