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:

mkdir foreign-key-demo
cd 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:

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

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

CREATE TABLE "public"."User" (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE "public"."Post" (
id SERIAL PRIMARY KEY,
title TEXT,
"authorId" INTEGER,
CONSTRAINT author FOREIGN KEY ("authorId") REFERENCES "public"."User" (id)
);

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:

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

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

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 "public"."AnotherUser" (
id SERIAL PRIMARY KEY,
"firstName" TEXT,
"lastName" TEXT,
UNIQUE ("firstName", "lastName")
);
CREATE TABLE "public"."AnotherPost" (
id SERIAL PRIMARY KEY,
title TEXT,
"authorFirstName" TEXT,
"authorLastName" TEXT,
FOREIGN KEY ("authorFirstName", "authorLastName") REFERENCES "public"."AnotherUser" ("firstName", "lastName")
);

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:

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

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 = "postgresql"
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=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:

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

npx prisma introspect

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

datasource db {
provider = "postgresql"
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])
}
model AnotherUser {
firstName String?
id Int @default(autoincrement()) @id
lastName String?
AnotherPost AnotherPost[]
@@unique([firstName, lastName], name: "AnotherUser_firstName_lastName_key")
}
model Post {
authorId Int?
id Int @default(autoincrement()) @id
title String?
User User? @relation(fields: [authorId], references: [id])
}
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'
}
]
}
Edit this page on GitHub