How to fix P3021: Foreign keys cannot be created on this database in Prisma

First, verify what database you're using and whether it supports foreign keys:

# Check your DATABASE_URL in .env.local or .env
cat .env.local | grep DATABASE_URL

Common databases that need special handling:
- PlanetScale (MySQL-compatible, serverless)
- Turso (SQLite-compatible, serverless)
- Neon (PostgreSQL, but with connection limits)
- CockroachDB (distributed SQL)
- SQLite (limited foreign key support)

For PlanetScale: Since February 2024, foreign key support is available. Check your database settings to enable it.

If you're using PlanetScale and it's a recent database, enable foreign key constraints:

1. Go to your PlanetScale dashboard
2. Navigate to your database settings
3. Look for "Foreign Key Constraints" setting
4. Toggle it to Enable
5. Save changes

Once enabled, you can use the default Prisma configuration. If enabling foreign keys isn't an option (older PlanetScale branches), proceed to the next step.

If your database doesn't support foreign keys, configure Prisma to emulate relations at the application level instead:

In your prisma/schema.prisma, update the datasource block:

datasource db {
  provider     = "mysql"           // or "sqlite", "postgresql", etc.
  url          = env("DATABASE_URL")
  relationMode = "prisma"          // Add this line
}

This tells Prisma to:
- Skip creating database-level foreign key constraints
- Handle referential actions (CASCADE, SET NULL, etc.) at the Prisma Client level
- Allow migrations to succeed on databases without foreign key support

After adding relationMode = "prisma", your database operations will work as before, but constraints are enforced by Prisma instead of the database.

When using relationMode = "prisma", Prisma doesn't automatically create indexes on foreign key fields. You must add them manually for good query performance:

For example, if you have a Blog with Posts and Comments:

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String
  blogId    Int
  blog      Blog    @relation(fields: [blogId], references: [id], onDelete: Cascade)

  comments  Comment[]

  @@index([blogId])  // Add this index!
}

model Comment {
  id      Int     @id @default(autoincrement())
  text    String
  postId  Int
  post    Post    @relation(fields: [postId], references: [id], onDelete: Cascade)

  @@index([postId])  // Add this index!
}

model Blog {
  id    Int     @id @default(autoincrement())
  name  String
  posts Post[]
}

The @@index([fieldName]) directive creates a database index on the foreign key field. This prevents full table scans and keeps query performance fast. Without indexes, lookups can become slow and expensive (especially on providers that bill per accessed row).

Once you've updated your schema with relationMode = "prisma" and added indexes, apply the changes:

# Option 1: Using db push (recommended for development)
npx prisma db push

# Option 2: Create and run a migration
npx prisma migrate dev --name add_indexes

You should see output like:

The following migration(s) have been created and applied:
  ./prisma/migrations/[timestamp]_init/migration.sql

All migrations have been successfully applied to your database.

If you still get P3021 errors after updating relationMode, ensure:
1. Your .env.local has the correct DATABASE_URL
2. You've saved the schema.prisma file
3. Your Prisma Client is regenerated (npx prisma generate)

After migration, test your relations to ensure they work correctly:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Create a blog
const blog = await prisma.blog.create({
  data: { name: 'My Blog' }
});

// Create a post (this will enforce the relation via Prisma)
const post = await prisma.post.create({
  data: {
    title: 'First Post',
    content: 'Hello World',
    blogId: blog.id
  }
});

// Create a comment
const comment = await prisma.comment.create({
  data: {
    text: 'Nice post!',
    postId: post.id
  }
});

console.log('Relations work correctly!');

If queries fail with referential action errors, check that your @@index directives are correctly placed and migrated.

Ensure your setup is fully compatible:

# Check Prisma version
npm list @prisma/client

# Recommended: Upgrade to latest Prisma if you're below v3.0
npm install @prisma/client@latest @prisma/cli@latest

# Regenerate Prisma Client
npx prisma generate

Compatibility matrix:

| Database | Provider | relationMode | Foreign Keys |
|----------|----------|--------------|--------------|
| PlanetScale (2024+) | mysql | foreignKeys | Supported |
| PlanetScale (pre-2024) | mysql | prisma | Not supported |
| Turso | sqlite | prisma | Limited support |
| Neon | postgresql | foreignKeys | Supported |
| CockroachDB | postgresql | foreignKeys | Supported |
| AWS Aurora | mysql/postgresql | foreignKeys | Supported |

Choose relationMode = "prisma" only if your database doesn't support foreign keys.