Putting the 'Relation' in 'Relational Database'
In conversations about Postgres, SQL and the like, you may hear the term relational database thrown around. In the previous material on SQL, you learned how to create, modify, and destroy rows and tables. How do 'relationships' (whatever that means) fit into that context?
Suppose that we had two separate tables of information in our database,
lunches (see below).
Each developer brings their own lunch,
and none of them want to eat each others' lunches,
so we have to make sure that each lunch lines up with the right developer.
How might we do that?
|1||salmon and tuna sushi rolls||chili|
|2||cheese sandwich on gluten-free bread||salad|
|3||roast beef sandwich||chips|
|4||chicken sandwich||steamed vegetables|
What if we were to put nametags on each of the lunches, so that we could know which developer brought which lunch?
|1||Johann||salmon and tuna sushi rolls||chili|
|2||Jenny||cheese sandwich on gluten-free bread||salad|
|3||Lecrecia||roast beef sandwich||chips|
|4||Ross||chicken sandwich||steamed vegetables|
We've now associated (i.e. related)
lunch record with a
But what if another developer with a duplicate name joins the mix?
It might be better to use something unique, like the
id column, instead.
|1||3||salmon and tuna sushi rolls||chili|
|2||1||cheese sandwich on gluten-free bread||salad|
|3||4||roast beef sandwich||chips|
|4||2||chicken sandwich||steamed vegetables|
developer_id column refers to data in the
but it's actually a column in the
This is what's known as a foreign key.
In terms of actual implementation in an RDBMS ( relational database management system ), a column can be defined as holding foreign keys using a modifier on a table definition called a constraint. Some examples of constraints are below.
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
Each of these allows you to put some bounds
on the values that can be put into specific columns.
The FOREIGN KEY constraint in particular makes sure that
values in that column are always valid
in the table that the column refers to.
When adding a FOREIGN KEY to a column, an INDEX is also usually added to that same column in order to speed access to matched rows. The combination of FOREIGN KEY and INDEX tells the RDBMS how you intend to use the tables you've related.
In the 'developers and lunches' example, one lunch was associated with one developer. This is called a 1 to 1 relationship. However, there are several other possible arangements, like 'one-to-many' and 'many-to-many' relationships; we'll look at the first of these now, and the second in later materials.