Foreign Key Constraints with knex.js to Ensure Database Atomicity
Tags: postgres, database, knex, atomicity
Published: 2021-02-23 16:25:31

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]([https://en.wikipedia.org/wiki/Atomicity_(database_systems)](https://en.wikipedia.org/wiki/Atomicity_(database_systems)))

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

![card_sets and flashcards relationship](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pb14m5dgsgdiirix2q97.png) 

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. 

```javascript
// 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)`]([https://knexjs.org/#Schema-references](https://knexjs.org/#Schema-references)) 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)`]([https://knexjs.org/#Schema-inTable](https://knexjs.org/#Schema-inTable)) 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)`]([https://knexjs.org/#Schema-onDelete](https://knexjs.org/#Schema-onDelete)) 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]([https://www.postgresql.org/message-id/4271.1233022978%40sss.pgh.pa.us](https://www.postgresql.org/message-id/4271.1233022978%40sss.pgh.pa.us))

## 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!