Fork and clone this repository.
By the end of this lesson, students should be able to:
- Add a foreign key reference to an existing table.
- Create a table with a foreign key reference.
- Insert a row which includes a reference to the id of a row in another table.
- Update a row setting a reference to the id of a row in another table.
- Retrieve rows from two tables using a
JOIN
condition
We've been talking about relational databases but haven't yet specified what constitutes a relationship.
In an RDBMS, relationships between tables are specified with a FOREIGN KEY
constraint on a column in one table that REFERENCES
the PRIMARY KEY
constraint on the id
column of another table. An INDEX
is usually added to the foreign key column to speed access to matched rows.
This reference and index tells the RDBMS how you intend to use related tables.
There are a number of standard cardinalities for relationships: 1-to-1, 1-to-(0 or 1), 1-to-(0 or more), and (0 or more)-to-(0 or more). The last two are more frequently called 1-to-many and many-to-many. We'll look at this last type of relationship in a following lesson.
We'll use sql-join
as the working database. We'll create it using the CREATE DATABASE command with sql-crud
specified as the template.
$ psql
psql (9.4.5)
Type "help" for help.
and=# CREATE DATABASE "sql-join" TEMPLATE "sql-crud";
CREATE DATABASE
and=# \c sql-join
You are now connected to database "sql-join" as user "and".
sql-join=#
We'll use the convention that a foreign key should be named for the singular of the table referenced with the column referenced appended after an underscore. So, if we're adding a reference to the cities
table and its id
column we'll create a column called city_id
. This convention should not be followed when there is a semantically superior name available.
- Constraints - An overview of the variety of constraints that PostgreSQL provides.
- CREATE TABLE - detailed documentation of PostgreSQL's version of the SQL
CREATE TABLE
command. - ALTER TABLE - detailed documentation of PostgreSQL's version of the SQL
ALTER TABLE
command. - Index Introduction - The introductory section of the chapter on indexes in PostgreSQL.
- CREATE INDEX - detailed documentation of PostgreSQL's version of the SQL
CREATE INDEX
command.
We'll add a place of birth to people which references a city in the city table. We'll use place_of_birth_id as the column name as this is a more semantic name for the column.
We'll create an addresses table which references the cities table. Then we'll add a reference from the people table to the addresses table.
We'll add an owner reference to the pets table.
We'll update or insert rows with appropriate values. Note that a foreign key constraint will disallow invalid values in the referencing column.
- UPDATE - detailed documentation of PostgreSQL's version of the SQL
UPDATE
command. - INSERT - detailed documentation of PostgreSQL's version of the SQL
INSERT INTO
command.
We'll set the place of birth for some people.
We'll insert an address associated with Somerville. Then we'll bulk load addresses and connect some of them to cities. After that, we'll set some people's current addresses.
We'll set the owner reference for some pets. Pick at least two people to be folks with too many pets.
The join
allows queries to return associated data from two tables as a single row.
- Joins Between Tables - An introduction to querying multiple tables
- SELECT - detailed documentation of PostgreSQL's version of the SQL
SELECT
command.
We'll count all the people by place of birth. Then we'll look at information about people born in a particular city or cities.
We'll query people, the associated addresses, and the cities associated with those addresses.
Let's check for hoarders. That's anyone with more than 3 dogs, 4 birds, 5, cats, or 20 fish.