Database Keys and Migrations

Keys are integral to Relational Database Management Systems (RDBMS). Each record or row can contain one or more keys, and there are several different types.

Records can be identified either by a single key or a combination of keys. Often, but not always, they'll be numbers. There are two types we'll concern ourselves with for now: primary and foreign keys.

Primary keys

Typically your id column will be a primary key. They have a unique constraint, which means that each value must be unique. You can't have two 1's, three 2's, etc. Imagine the chaos if the users in your database could share user IDs!

Here's a Knex.js migration that creates a simple table with a primary key:

export const up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.integer('id').primary()
    table.string('name')
  })
}

export const down = function(knex) {
  return knex.schema.dropTable('users')
}

users

Primary keys can't be null or 0. Every record in a table with a primary key has to have a key assigned.

Often we just want primary keys to be an incrementing integer. The .increments() function makes sure that when you add a new row, an id will be assigned with the value of the highest existing id + 1. This will automatically assign the id to be the primary key.

export const up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id')
    table.string('name')
  })
}

export const down = function(knex) {
  return knex.schema.dropTable('users')
}

Foreign keys

Often we'll need to relate a record in one table to a record in another. Foreign keys link records from different tables together. Using constraints, they can help maintain the integrity of the data and enable joins to combine tables.

Here's how Knex defines foreign keys:

export async function up(knex) {
  await knex.schema.createTable('breeds', table => {
    table.increments('id');
    table.string('name');
  });

  await knex.schema.createTable('dogs', table => {
    table.increments('id');
    table.string('name');
    table.integer('breed_id').references('breeds.id');
  });
}

export async function down(knex) {
  await knex.schema.dropTable('dogs');
  await knex.schema.dropTable('breeds');
}

Notice the .references('breeds.id')? That tells the database that we want dogs.breed_id to refer to breeds.id: we're linking the two tables together using breed_id as the key. We use async/await so that we don't cause a database error while running the migration: the column we're referencing has to exist first, after all! You might also see migrations written as two separate files, one for each individual table.

dogs

breeds

Put another way, breed_id is a foreign key that references id on the breeds table.

Constraints

We've touched on the unique constraint that applies to primary keys (no two can be the same value). Foreign keys can have constraints too, that define what should happen when an attempt is made to remove or alter the record that the key is pointing to.

Take the above table for example. Say someone tries to delete the 'Mutt' row in the breeds table, with id 3. We already have a record that refers to that row: 'Daisy' in dogs would be left without a breed! This seems like a bad thing, and it is. We don't want IDs that point off into nowhere, referring to no record. If someone later managed to add a completely different breed having an id of 3, Daisy's breed would be changed without anyone asking her permission.

The solution is to add a constraint defining what should happen if a breed is deleted. We call this constraint ON DELETE. There are several possible values for the constraint. Two of the most important are RESTRICT and CASCADE.

RESTRICT means that the attempt to remove the 'Mutt' record will fail if any of the records in the dogs table are mutts. Each dog would have to have their breed changed before the attempt to delete 'Mutt' could succeed.

CASCADE means that all dogs that are mutts will be deleted when the 'Mutt' record is deleted! For obvious reasons, be careful with this one.

We can also apply similar values to an ON UPDATE constraint, which as you might have guessed defines what the database should do when a record in breeds is changed, but not deleted.

Here's how Knex handles these constraints:

  table.integer('breed_id')
    .references('breeds.id')
    .onDelete('RESTRICT')
    .onUpdate('CASCADE')

In other words, when someone tries to remove a breed that is referenced in dogs, don't allow it; when someone merely updates a breed, the change spreads to all dogs of that breed. For example, if 'Mutt's id was changed from 3 to 4, Daisy's breed_id would change to 4 as well.