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-democd 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 onAnotherUser
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'sauthorId
column which points to theid
field of theUser
table. - The table
AnotherPost
has a multi-column foreign key on it'sauthorFirstName
andauthorLastName
columns which point to thefirstName
andlastField
columns of theAnotherUser
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 -ynpm 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()) @idtitle String?AnotherUser AnotherUser? @relation(fields: [authorFirstName, authorLastName], references: [firstName, lastName])@@index([authorFirstName, authorLastName], name: "authorFirstName")}model AnotherUser {firstName String?id Int @default(autoincrement()) @idlastName String?AnotherPost AnotherPost[]@@unique([firstName, lastName], name: "firstName")}model Post {authorId Int?id Int @default(autoincrement()) @idtitle String?User User? @relation(fields: [authorId], references: [id])@@index([authorId], name: "author")}model User {id Int @default(autoincrement()) @idname 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'}]}