Over the course of building Flashcards, my Quizlet-like clone, I wanted to make sure that if I delete a
card_set all of its
flashcards would be deleted as well. I tried to handle this myself, but this became a nightmare of bad logic and edge cases. Eventually, I took a step back and tried to examine the problem I was really trying to solve. My database is my single source of truth for all data in my application. Is it possible for my database to safely handle the above operation? It is! By adding foreign key constraints to my
knex migrations I can take steps to ensure database atomicity within my Postgres database.
"An atomic transaction is an indivisible and irreducible series of database operations such that either all occur, or nothing occurs." - Wikipedia
This means my database will guarantee that if I try and delete a
card_set and its
flashcards, either that
card_set and it's
flashcards are both deleted, or neither are deleted.
A foreign key is a reference to the primary key of another table. Foreign keys make it easy for us to establish relationships between tables.
Before we jump into the code, let's look at the relationship I have setup for my
card_sets table is a parent table to my
flashcards child table.
A parent can have many children but a child can only have one parent, just like a
card_set can have many
flashcards, but a
flashcard can only belong to a singular
card_set. This relationship can also be described as a "has many/belongs" to relationship. A
card_set has many
flashcards, but a
flashcard belongs to only one
In order to establish this "parent/child" or "has many/belongs to" relationship, I have to set up foreign key constraints. This is what my foreign key constraint looks like with
knex, the SQL query builder I am using.
// Part of My Flashcards Migration File // This code creates my "card_set_id" column as a // not nullable UUID type table .uuid("card_set_id") .notNullable() .references("id") .inTable("card_sets") .onDelete("CASCADE");
These three functions will help us define our foreign keys and their behavior:
column.references(column) function tells us what primary key value to reference as our foreign key.
In our code above we are saying we are going to reference the
id column in some table (we haven't defined which table yet!) as the primary key to our
card_set_id foreign key.
Now we have to specify which table's
id column we would like to use the primary key value from.
column.inTable(table) function lets us define exactly which table we'll get our primary key value to use as a foreign key.
In our code above we explicitly use the
card_sets table. Now we have bound the two, and formed a relationship between tables
flashcards.card_set_id is the foreign key to
column.onDelete(command) function allows us to define how we'd like all child rows with this foreign key (
flashcards.card_set_id) to behave when their referenced parent row is deleted (
I'm working with Postgres, keep in mind not all SQL database implementations will accept these commands:
- CASCADE - When the parent row is deleted, all children rows referencing the parent are deleted as well
- SET NULL - When the parent row is deleted, all children rows with a foreign key referencing the parent row will be set to the value "NULL"
- RESTRICT - If a child row references a parent row, no parent and or child rows will be deleted
- NO ACTION
Now the problem is solved! If I delete a
card_set my PostgreSQL database will guarantee that all
flashcards associated with that
card_set are deleted as well.
Postgres will handle the entire transaction. If the transaction fails, my
card_set and its associated
flashcards will return to the state they were in before the transaction occurred. If the transaction succeeds, then I know both the
card_set and associated
flashcards are deleted!