When we have a situation where one table is related to another table in a database, we may want to bind those tables back together in a query. For example, let’s say we have a person table and an email table. If we want a list of names and associated emails, we would need to join these tables together.

To maintain data integrity and ensure that we can join tables together correctly, we can use another type of key called a foreign key. A foreign key is a key that references a column in another table.

Where do we place this foreign key?

Should it be in the person table or email table?

To answer this question, we need to figure out how person is related to email. Does creating a person record require that an email record exists as well? This is not usually the case. A person can have no email address or one or more email addresses. So when creating a record in the person table, we don’t insist that this person should have a record in the email table as well.

Does creating an email record require that a valid person record exists? This is usually the case, since we shouldn’t create an email address for a non-existent person. Hence, we should place the foreign key in the email table to ensure that a valid record in the person table must pre-exist before adding a record in the email table.

To designate a foreign key on a single column in PostgreSQL, we use the REFERENCES keyword:

CREATE TABLE person (
  id integer PRIMARY KEY,
  name varchar(20),
  age integer
);

CREATE TABLE email (
  email varchar(20) PRIMARY KEY,
  person_id integer REFERENCES person(id),
  storage integer,
  price money
);

Now we can use the following query to return a table of names and associated emails:

SELECT person.name AS name, email.email AS email
FROM person, email
WHERE person.id = email.person_id;