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.
id | name | |
---|---|---|
1 | John | |
2 | Sarah | |
3 | Jane |
Creating a Primary Key
When creating a table, we can specify a column as a primary key by using the PRIMARY KEY
constraint.
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.
users | posts |
---|---|
id | id |
name | title |
body | |
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.
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.
The above query will return all the posts for the user with an id of 1.