Skip to content

Tables

Relationships

There are three types of relationships between entities:

  • One to One
  • One to Many
  • Many to Many

One to One

A one to one relationship is where one entity is related to another entity.

For example, a user can have one profile and a profile can belong to one user.

One to Many

A one to many relationship is where one entity is related to many entities.

For example, a user can have many posts but a post can only belong to one user.

Many to Many

A many to many relationship is where many entities are related to many entities.

For example, a user can have many roles and a role can belong to many users.

Primary Keys

A primary key is a column that uniquely identifies each row in a table.

For example, if we have a users table, we can use the id column as the primary key.

idnameemail
1John
2Sarah
3Jane

Creating a Primary Key

When creating a table, we can specify a column as a primary key by using the PRIMARY KEY constraint.

CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
);

Foreign Keys

When we have a table that references another table, we call the column that references the other table a foreign key.

An example would be the below schema which has a users table and a posts table. The posts table has a user_id column which references the id column in the users table.

usersposts
idid
nametitle
emailbody
user_id

As the posts collumn references the users table, we call it a foreign key.

Creating a Foreign Key

When creating a table, we can specify a column as a foreign key by using the references method.

CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title VARCHAR(255),
body TEXT,
user_id INTEGER
FOREIGN KEY (user_id) REFERENCES users(id)
);

Table Joins

When we have two tables that are related to each other, we can use a JOIN to combine the data from both tables into a single result set.

For example, if we have a users table and a posts table, we can use a JOIN to get all the posts for a given user.

SELECT * FROM posts
JOIN users ON users.id = posts.user_id
WHERE users.id = 1;

The above query will return all the posts for the user with an id of 1.

| id | title | body | user_id | id | name | email |
|----|-------|------|---------|----|------|-------|
| 1 | ... | ... | 1 | 1 | ... | ... |
| 2 | ... | ... | 1 | 1 | ... | ... |
| 3 | ... | ... | 1 | 1 | ... | ... |