Fix Prisma foreign key constraint violation on create

Problem

Inserting a record that references a related record fails with:

prisma:error
Invalid `prisma.issue.create()` invocation:

Foreign key constraint violated on the constraint: `issues_team_id_fkey`

This happens when the referenced parent record does not exist yet:

// Fails: team with this ID doesn't exist in the database
await prisma.issue.create({
  data: {
    id: "issue-1",
    title: "Bug report",
    teamId: "team-abc", // Foreign key points to non-existent team
  },
});

Solution

Option 1: Create parent records first with connectOrCreate (recommended)

await prisma.issue.create({
  data: {
    id: "issue-1",
    title: "Bug report",
    team: {
      connectOrCreate: {
        where: { id: "team-abc" },
        create: { id: "team-abc", name: "Engineering" },
      },
    },
  },
});

Option 2: Use a transaction to ensure insertion order

await prisma.$transaction(async (tx) => {
  // Create parent first
  await tx.team.upsert({
    where: { id: "team-abc" },
    update: {},
    create: { id: "team-abc", name: "Engineering" },
  });

  // Then create child
  await tx.issue.create({
    data: {
      id: "issue-1",
      title: "Bug report",
      teamId: "team-abc",
    },
  });
});

Option 3: Batch inserts with createMany and sorted dependency order

// When syncing bulk data, sort by dependency graph
await prisma.$transaction([
  prisma.team.createMany({ data: teams, skipDuplicates: true }),
  prisma.issue.createMany({ data: issues, skipDuplicates: true }),
]);

Why It Works

Relational databases enforce referential integrity through foreign key constraints. When a child table row references a parent table row via a foreign key, the parent row must exist before the child can be inserted. Prisma surfaces this as a PrismaClientKnownRequestError with the constraint name. Using connectOrCreate lets Prisma handle the existence check and creation atomically. Transactions ensure the parent insert commits before the child insert runs. The skipDuplicates flag on createMany makes bulk syncs idempotent.

Context

• Prisma 4.x+ with PostgreSQL, MySQL, or SQLite
• Common in data sync pipelines, seed scripts, and webhook handlers where records arrive out of order
• Use upsert instead of create when records may already exist
• For large batch syncs, sort records topologically by their foreign key dependencies before inserting
• The constraint name in the error (e.g., issues_team_id_fkey) tells you which relation is missing