Relationships in PostgreSQL: A Comprehensive Guide - Day -5 of Postgres Learning Journey

PostgreSQL, a robust relational database management system, empowers developers with the ability to establish and manage relationships between tables. In this comprehensive guide, we will delve into the intricate world of relations within PostgreSQL, covering topics such as understanding foreign keys, adding relationships, updating foreign key columns, performing inner and left joins, and dealing with record deletions involving foreign keys. Each topic will be accompanied by illustrative code examples and the corresponding output results.

Table of Contents

1. Understanding Foreign Keys / Relationships

2. Adding Relationships (FOREIGN KEY)

3. Updating Foreign Key Columns

4. Inner Joins

5. Left Joins

6. Deleting Records With Foreign Key

7. Conclusion

1. Understanding Foreign Keys / Relationships

A foreign key establishes a link between two tables based on the values of a specific column, typically called the referencing column in the child table and the referenced column in the parent table. This relationship ensures data integrity and enables you to perform operations across related tables.

2. Adding Relationships (FOREIGN KEY)

To add a relationship between tables, you use the FOREIGN KEY constraint. Let's consider two hypothetical tables: customers and orders.

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

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example, the orders table has a foreign key column customer_id that references the customer_id column in the customers table.

Result Output:

Table "public.customers"
   Column    |         Type          | Collation | Nullable |              Default              
-------------+-----------------------+-----------+----------+-----------------------------------
 customer_id | integer               |           | not null | nextval('customers_customer_id_seq'::regclass)
 name        | character varying(100) |           |          | 
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)

Table "public.orders"
   Column   |  Type   | Collation | Nullable |              Default              
------------+---------+-----------+----------+-----------------------------------
 order_id   | integer |           | not null | nextval('orders_order_id_seq'::regclass)
 customer_id| integer |           |          | 
 order_date | date    |           |          | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

3. Updating Foreign Key Columns

If you need to update the foreign key column in the child table, you can do so with an UPDATE statement.

UPDATE orders
SET customer_id = 2
WHERE order_id = 1;

This query updates the customer_id of the order with order_id 1 to 2.

Result Output:

UPDATE 1

4. Inner Joins

An inner join combines rows from two or more tables based on a related column. It returns only the matching rows.

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

This query retrieves the order IDs and customer names for orders along with their corresponding customers.

Result Output:

 order_id |   name   
----------+----------
        1 | Alice
        2 | Bob
        3 | Carol
(3 rows)

5. Left Joins

A left join returns all rows from the left table and the matching rows from the right table. If there's no match, NULL values are filled in.

SELECT orders.order_id, customers.name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

This query retrieves the order IDs and customer names for all orders, even if some orders do not have corresponding customers.

Result Output:

 order_id |   name   
----------+----------
        1 | Alice
        2 | Bob
        3 | Carol
        4 | NULL
(4 rows)

6. Deleting Records With Foreign Key

Deleting records with foreign key constraints requires careful consideration. By default, PostgreSQL does not allow deleting a parent record if there are child records referencing it.

DELETE FROM customers WHERE customer_id = 1;

If you attempt to execute this query, PostgreSQL will raise an error if there are orders referencing the customer with customer_id 1.

Result Output:

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (customer_id)=(1) is still referenced from table "orders".

7. Conclusion

Understanding and effectively utilizing relationships in PostgreSQL is essential for maintaining data integrity and extracting meaningful insights from your database. From establishing foreign keys to performing various types of joins, these relational concepts are foundational to database design and querying.

By following the examples and explanations provided in this blog, you'll be well-equipped to navigate the complexities of relations within PostgreSQL, ensuring the accuracy and relevance of your data while optimizing your query operations.