Overview

This page explains how to configure foreign key constraints in your PostgreSQL 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 PostgreSQL database server running
  • the createdb command line utility
  • the psql command line client for PostgreSQL
  • Node.js installed on your machine

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:

1mkdir foreign-key-demo
2cd foreign-key-demo

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

1createdb ForeignKeyDemo

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

1psql -d ForeignKeyDemo -c "\dt"

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:

1CREATE TABLE "public"."User" (
2 id SERIAL PRIMARY KEY,
3 name TEXT
4);
5
6CREATE TABLE "public"."Post" (
7 id SERIAL PRIMARY KEY,
8 title TEXT,
9 "authorId" INTEGER,
10 CONSTRAINT author FOREIGN KEY ("authorId") REFERENCES "public"."User" (id)
11);

Note: The quotes are added to the "authorId" column name to maintain the casing (otherwise PostgreSQL interprets column names as all-lowercase).

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

1psql ForeignKeyDemo < 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.


In the code above, you created the unique constraint as a table constraint. Alternatively, you can define it as a column constraint. There's no practical difference between the two, the alternative is just added for completeness.

To add the foreign key constraint as a column constraint, you need to adjust your SQL statement for creating the Post table to look as follows:

1CREATE TABLE "public"."Post" (
2 id SERIAL PRIMARY KEY,
3 title TEXT,
4 "authorId" INTEGER REFERENCES "public"."User" (id)
5);

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:

1CREATE TABLE "public"."AnotherUser" (
2 id SERIAL PRIMARY KEY,
3 "firstName" TEXT,
4 "lastName" TEXT,
5 UNIQUE ("firstName", "lastName")
6);
7
8CREATE TABLE "public"."AnotherPost" (
9 id SERIAL PRIMARY KEY,
10 title TEXT,
11 "authorFirstName" TEXT,
12 "authorLastName" TEXT,
13 FOREIGN KEY ("authorFirstName", "authorLastName") REFERENCES "public"."AnotherUser" ("firstName", "lastName")
14);

Note: The quotes are added to the "firstName" and "lastName" column names to maintain the casing (otherwise PostgreSQL interprets column names as all-lowercase). 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:

1psql ForeignKeyDemo < 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:

1npm init -y
2npm 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:

1datasource postgresql {
2 provider = "postgresql"
3 url = env("DATABASE_URL")
4}

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:

1DATABASE_URL=postgresql://__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:

1DATABASE_URL=postgresql://janedoe:mypassword@localhost:5432/ForeignKeyDemo

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

1npx prisma introspect

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

1datasource postgresql {
2 provider = "postgresql"
3 url = env("DATABASE_URL")
4}
5
6model AnotherPost {
7 authorFirstName String?
8 authorLastName String?
9 id Int @default(autoincrement()) @id
10 title String?
11 AnotherUser AnotherUser? @relation(fields: [authorFirstName, authorLastName], references: [firstName, lastName])
12}
13
14model AnotherUser {
15 firstName String?
16 id Int @default(autoincrement()) @id
17 lastName String?
18 AnotherPost AnotherPost[]
19
20 @@unique([firstName, lastName], name: "AnotherUser_firstName_lastName_key")
21}
22
23model Post {
24 authorId Int?
25 id Int @default(autoincrement()) @id
26 title String?
27 User User? @relation(fields: [authorId], references: [id])
28}
29
30model User {
31 id Int @default(autoincrement()) @id
32 name String?
33 Post Post[]
34}

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):

1generator client {
2 provider = "prisma-client-js"
3}

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

1npx 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:

1const { PrismaClient } = require('@prisma/client');
2
3const prisma = new PrismaClient();
4
5async function main() {
6 const userWithPost = await prisma.user.create({
7 data: {
8 name: 'Alice',
9 Post: {
10 create: {
11 title: 'Hello World from Alice',
12 },
13 },
14 },
15 include: {
16 Post: true,
17 },
18 });
19 console.log(userWithPost);
20
21 const anotherUserWithPost = await prisma.anotherUser.create({
22 data: {
23 firstName: 'Bob',
24 lastName: 'Smith',
25 AnotherPost: {
26 create: {
27 title: 'Hello World from Bob',
28 },
29 },
30 },
31 include: {
32 AnotherPost: true,
33 },
34 });
35 console.log(anotherUserWithPost);
36}
37
38main();

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

Run the code with this command:

1node index.js

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

1{
2 id: 1,
3 name: 'Alice',
4 Post: [ { authorId: 1, id: 1, title: 'Hello World from Alice' } ]
5}
6{
7 firstName: 'Bob',
8 id: 1,
9 lastName: 'Smith',
10 AnotherPost: [
11 {
12 authorFirstName: 'Bob',
13 authorLastName: 'Smith',
14 id: 1,
15 title: 'Hello World from Bob'
16 }
17 ]
18}
Edit this page on Github