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.

What Is Database Atomicity?

"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.

What Is A Foreign Key?

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 and flashcards. 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 card_set.

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:

References

The 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.

inTable

The 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 card_sets.id

onDelete

The 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 (card_sets.id).

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

Guaranteed Database Atomicity

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!