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
);
Column | Type | Collation | Nullable | Default |
---|---|---|---|---|
recipe_id | integer | not null | generated always as identity | |
title | character varying(255) | 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
);
Column | Type | Collation | Nullable | Default |
---|---|---|---|---|
photo_id | integer | not null | generated always as identity | |
url | character varying(255) | not null | ||
recipe_id | integer |
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 andrecipe_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 settingrecipe_id
in the corresponding photo records tonull
. - 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↗.