Primary and Foreign Keys in PostgreSQL with Easy Examples

Today, I’ll break down these essential database concepts with easy-to-understand code examples and show you their outputs. By the end of this post, you’ll have a solid grasp of how primary and foreign keys work in PostgreSQL.

What Are Primary Keys?

A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures that no duplicate or null values exist in that column.

Creating a Table with a Primary Key

Let’s create a simple table named employees with a primary key.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

In this example, employee_id is the primary key. The SERIAL keyword ensures that PostgreSQL generates a unique ID for each new row.

Inserting Data

Let’s insert some data into the employees table.

INSERT INTO employees (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com'),
('Robert', 'Johnson', 'robert.johnson@example.com');

Querying Data

Now, let’s query the data to see what we have.

SELECT * FROM employees;

Output:

 employee_id | first_name | last_name  |           email           
-------------+------------+------------+---------------------------
           1 | John       | Doe        | john.doe@example.com
           2 | Jane       | Smith      | jane.smith@example.com
           3 | Robert      | Johnson    | robert.johnson@example.com

What Are Foreign Keys?

A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a relationship between the two tables.

Creating a Table with a Foreign Key

Let’s create another table named departments and a third table named employee_departments to establish a relationship between employees and departments.

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employee_departments (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    department_id INTEGER REFERENCES departments(department_id)
);

In this example, employee_departments has foreign keys employee_id and department_id that reference the primary keys in the employees and departments tables, respectively.

Inserting Data into Departments

Let’s insert some departments into the departments table.

INSERT INTO departments (department_name) VALUES
('Engineering'),
('Marketing'),
('Sales');

Inserting Data into Employee Departments

Now, let’s assign employees to departments.

INSERT INTO employee_departments (employee_id, department_id) VALUES
(1, 1), -- John Doe in Engineering
(2, 2), -- Jane Smith in Marketing
(3, 3); -- Robert Johnson in Sales

Querying Data to Show Relationships

Let’s query the employee_departments table to see the relationships.

SELECT * FROM employee_departments;

Output:

 id | employee_id | department_id 
----+-------------+---------------
  1 |           1 |             1
  2 |           2 |             2
  3 |           3 |             3

Joining Tables to Get Comprehensive Data

To see a more comprehensive view, let’s join the employees, departments, and employee_departments tables.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN employee_departments ed ON e.employee_id = ed.employee_id
JOIN departments d ON ed.department_id = d.department_id;

Output:

 first_name | last_name | department_name 
------------+-----------+-----------------
 John       | Doe       | Engineering
 Jane       | Smith     | Marketing
 Robert     | Johnson   | Sales

Conclusion

Primary and foreign keys are fundamental concepts in PostgreSQL that help maintain data integrity and establish relationships between tables. By using primary keys, you ensure that each record in a table is unique. Foreign keys, on the other hand, help you create links between tables, enabling complex queries and relationships.

I hope these simple examples have helped you understand primary and foreign keys better. Happy coding!

---