PostgreSQL Foreign Keys

If you're going to have have two tables reference each other, use foreign keys where possible.

It forces useful constraints to make sure delete and update behaviors are intentional and it makes the queries faster.

Let's assume you have two tables in your database.

RECIPES table

CREATE TABLE recipes (
  recipe_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title VARCHAR ( 255 ) UNIQUE NOT NULL,
  body TEXT
);

Indexes:
    "recipes_pkey" PRIMARY KEY, btree (recipe_id)
    "recipes_title_key" UNIQUE CONSTRAINT, btree (title)

RECIPES PHOTOS table

CREATE TABLE recipes_photos (
  photo_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  url VARCHAR(255) NOT NULL,
  recipe_id INTEGER REFERENCES recipes(recipe_id) ON DELETE CASCADE
);

Indexes:
    "recipes_photos_pkey" PRIMARY KEY, btree (photo_id)
Foreign-key constraints:
    "recipes_photos_recipe_id_fkey" FOREIGN KEY (recipe_id) REFERENCES recipes(recipe_id) ON DELETE CASCADE

• The REFERENCES portion means it's going to be a foreign key. You tell it what it's going to match up to. recipes is the table and recipe_id is the name of the column it'll match. In our case those are the same name, but it doesn't have to be. It must be the primary key of the other table.
• Then you need to tell it what to do when you delete something. With ON DELETE CASCADE you say "if the row in the other table gets deleted, delete this one too." So if we delete something from recipes, it will automatically delete all its photos.
• You can also do ON DELETE SET NULL which does exactly what it says it does. In this example that would mean setting recipe_id in the corresponding photo records to null.
• There's also ON DELETE NO ACTION which will error out if you try to delete something from recipes if there are still photos left. This forces developers to clean up photos before deleting recipes.
• Use ON UPDATE if you need to handle some synced state between the two tables.

The foreign key constraint also prevents you from adding orphan photos. If you try to add a photo record with a recipe_id that does not match a record in the recipe table you will get a warning.

In the example above you can technically add a photo record without any recipe_id and that would work. Perhaps you want to add a photo and give it a foreign key later. However that could result in an orphan photo. It's best to also set recipe_id to NOT NULL so that you're required to provide a value...and as previously stated, that value must match an existing record in the recipe table.

NOTE: this example represents a one-to-many relationship.

• Each recipe has many photos.
• Each photo belongs to one recipe.

From the Complete Intro to SQL & PostgreSQL↗ course on FEM↗ taught by Brian Holt↗.