Foreign keys / Relations (MySQL)

Overview

This page explains how to configure foreign key constraints in your MySQL database. Foreign keys are used to represent relations in your database.

In this guide, you'll create two tables where one references the other via a foreign key. Foreign keys can be defined on a single column or on multiple columns. This guide covers both approaches.

At the end of the guide, you'll introspect your database to reflect the foreign key in the Prisma schema, then you'll generate Prisma Client and write a simple Node.js script to test the foreign key relation.

Prerequisites

In order to follow this guide, you need:

• a MySQL database server running
• the mysql command line client for MySQL
• Node.js installed on your machine

Note: If you are using a version of MySQL where MyISAM is the default engine, you must specify ENGINE = InnoDB; when you create a table.

1. Create a new database and project directory

Start by creating a project directory where you can put the files you'll create throughout this guide:

mkdir foreign-key-demo
cd foreign-key-demo

Next, make sure that your MySQL database server is running. Then execute the following command in your terminal to create a new database called ForeignKeyDemo:

mysql -e 'CREATE DATABASE `ForeignKeyDemo`;'

You can validate that the database was created by running the SHOW TABLES command which lists all tables (relations) in your database (right now there are none):

mysql -e 'SHOW TABLES in `ForeignKeyDemo`;'

2. Create two tables with a single-column foreign key constraint

In this section, you'll create two tables where one references the other via a single-column foreign key in the ForeignKeyDemo database.

Create a new file called single-column-foreign-key.sql and add the following code to it:

CREATE TABLE `ForeignKeyDemo`.`User` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(256)
);
CREATE TABLE `ForeignKeyDemo`.`Post` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `authorId` INT,
  CONSTRAINT `author` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`)
);

Now run the SQL statement against your database to create the two tables:

mysql < single-column-foreign-key.sql

Congratulations, you just created two tables called User and Post in the database. The Post table references the User table via the foreign key defined on the authorId column.

3. Create a table with a multi-column foreign key constraint

In this section, you'll create two tables where one references the other via a single-column foreign key in the ForeignKeyDemo database.

Create a new file called multi-column-foreign-key.sql and add the following code to it:

CREATE TABLE `ForeignKeyDemo`.`AnotherUser` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `firstName` VARCHAR(256),
  `lastName` VARCHAR(256),
  UNIQUE (`firstName`, `lastName`)
);
CREATE TABLE `ForeignKeyDemo`.`AnotherPost` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` TEXT,
  `authorFirstName` VARCHAR(256),
  `authorLastName` VARCHAR(256),
  FOREIGN KEY (`authorFirstName`, `authorLastName`) REFERENCES `AnotherUser`(`firstName`, `lastName`)
);

Note: The UNIQUE constraint on AnotherUser is needed to be able to reference the columns as foreign keys.

Now run the SQL statement against your database to create a new table called AnotherUser:

mysql < multi-column-foreign-key.sql

Congratulations, you just created two tables called AnotherUser and AnotherPost in the database. The AnotherPost table references the AnotherUser table via the foreign key defined on the authorFirstName and authorLastName columns.

4. Introspect your database with Prisma

In the previous sections, you created two different foreign key constraints using four total tables:

• The table Post has a singe-column foreign key on it's authorId column which points to the id field of the User table.
• The table AnotherPost has a multi-column foreign key on it's authorFirstName and authorLastName columns which point to the firstName and lastField columns of the AnotherUser table.

In this section you'll introspect your database to generate the Prisma models for these tables.

To start, set up a new Node.js project and add the prisma CLI as a development dependency:

npm init -y
npm install prisma --save-dev

In order to introspect your database, you need to tell Prisma how to connect to it. You do so by configuring a datasource in your Prisma schema.

Create a new file called schema.prisma and add the following code to it:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

The database connection URL is set via an environment variable. The Prisma CLI automatically supports the dotenv format which automatically picks up environment variables defined in a file called .env.

Create a new file called .env and set your database connection URL as the DATABASE_URL environment variable:

DATABASE_URL=mysql://__USER__:__PASSWORD__@__HOST__:__PORT__/ForeignKeyDemo

In the above code snippet, you need to replace the uppercase placeholders with your own connection details. For example, if your database is running locally it could look like this:

DATABASE_URL=mysql://janedoe:mypassword@localhost:3306/ForeignKeyDemo

With both the schema.prisma and .env files in place, you can run Prisma's introspection with the following command:

npx prisma introspect

This command introspects your database and for each table adds a Prisma model to the Prisma schema:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}
model AnotherPost {
  authorFirstName String?
  authorLastName  String?
  id              Int          @default(autoincrement()) @id
  title           String?
  AnotherUser     AnotherUser? @relation(fields: [authorFirstName, authorLastName], references: [firstName, lastName])
  @@index([authorFirstName, authorLastName], name: "authorFirstName")
}
model AnotherUser {
  firstName   String?
  id          Int           @default(autoincrement()) @id
  lastName    String?
  AnotherPost AnotherPost[]
  @@unique([firstName, lastName], name: "firstName")
}
model Post {
  authorId Int?
  id       Int     @default(autoincrement()) @id
  title    String?
  User     User?   @relation(fields: [authorId], references: [id])
  @@index([authorId], name: "author")
}
model User {
  id   Int     @default(autoincrement()) @id
  name String?
  Post Post[]
}

5. Generate Prisma Client

To validate whether the foreign key constraints work, you'll now generate Prisma Client and send a few sample queries to the database to test the relations.

First, add a generator block to your Prisma schema (typically added right below the datasource block):

generator client {
  provider = "prisma-client-js"
}

Run the following command to install and generate Prisma Client in your project:

npx prisma generate

Now you can use Prisma Client to send database queries in Node.js.

6. Validate the foreign key constraints in a Node.js script

Create a new file called index.js and add the following code to it:

const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()
async function main() {
  const userWithPost = await prisma.user.create({
    data: {
      name: 'Alice',
      Post: {
        create: {
          title: 'Hello World from Alice',
        },
      },
    },
    include: {
      Post: true,
    },
  })
  console.log(userWithPost)
  const anotherUserWithPost = await prisma.anotherUser.create({
    data: {
      firstName: 'Bob',
      lastName: 'Smith',
      AnotherPost: {
        create: {
          title: 'Hello World from Bob',
        },
      },
    },
    include: {
      AnotherPost: true,
    },
  })
  console.log(anotherUserWithPost)
}
main()

In this code, you're creating two User records, each with a related Post record.

Run the code with this command:

node index.js

The following output indicates that the foreign key constraint is working as intended:

{
  id: 1,
  name: 'Alice',
  Post: [ { authorId: 1, id: 1, title: 'Hello World from Alice' } ]
}
{
  firstName: 'Bob',
  id: 1,
  lastName: 'Smith',
  AnotherPost: [
    {
      authorFirstName: 'Bob',
      authorLastName: 'Smith',
      id: 1,
      title: 'Hello World from Bob'
    }
  ]
}