Find file History
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
README.md
sql-references_notes.pdf

README.md

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, developers and 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?

developers

id first_name last_name favorite language
1 Johann Folta C
2 Jenny Martinez JavaScript
3 Lucrecia Fazah Ruby
4 Ross Degnen LOLCODE

lunches

id main_course side_dish
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?

lunches

id developer main_course side_dish
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) each lunch record with a developer record. 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.

id developer_id main_course side_dish
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

The developer_id column refers to data in the developers table, but it's actually a column in the lunches table. 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
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK

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 id values 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.