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!
Top comments (0)