Unique constraint failed on the constraint: `User_Account_userId_key` in prisma

Question

Hi I have three models

model User {
  user_id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  User_Account User_Account[]
}

model Account {
  account_id Int @id @default (autoincrement()) @unique
  email String 
  bank String
  createdAt DateTime @default(now())
  User_Account User_Account[]

}
model User_Account {
  id Int @id @default(autoincrement())
  accountId Int 
  userId Int 
  User User @relation(fields: [userId], references: [user_id])
  Account Account @relation(fields: [accountId], references: [account_id])
}

I am trying to seed my db like this

const data = [
    {
      id: 1,
      email: '[email protected]',
      name: 'Pranit1',
      bank: 'VCB',
      ids: [1,1]
    },
    {
      id: 2,
      email: '[email protected]',
      name: 'Pranit1',
      bank: 'ACB',
      ids: [1,2]
    },
    {
      id: 3,
      email: '[email protected]',
      name: 'Pranit3',
      bank: 'VCB',
      ids: [2,3]
    }
  ]
  const users = await prisma.$transaction(
    data.map(user =>
      prisma.user.upsert({
        where: { email: user.email },
        update: {},
        create: { name: user.name,
        email:user.email },
      })
    )
  );
  
  const accounts = await prisma.$transaction(
    data.map(account => 
      prisma.account.upsert({
        where: { account_id: account.id },
        update: {},
        create: { bank: account.bank ,
          email :account.email },
      })
    )
  );

  const user_accounts = await prisma.$transaction(
    data.map(uacc =>{
      console.log(uacc);
      return prisma.user_Account.upsert({
        where: { id: uacc.id },
        update: {id: uacc.id},
        create:{
          userId: uacc.ids[0],
        accountId: uacc.ids[1] },
      })}
    )
  );

However I am getting an

Unique constraint failed on the constraint: User_Account_userId_key

The data in prisma studio is generated as shown in the image

I am simply trying to create users and accounts and a user can be associated with multiple accounts. Their relation is shown in the User_Account table. I cant see why I am getting a unique constraint error when I dont have the @unique tag on userId

Relapse

What kind of database are you using? Tried to reproduce but it works fine for me using Postgres – Terminate

Answer 1

I was unable to reproduce the error on my side. But I suspect you already had records on the DB and they conflict with the ids of your seeder. Also, there are some improvements you could make on your schema for being simpler.

1. Since you don't have any extra details on the many-to-many relation you can get rid of the User_Account model and let Prisma handle it for you.
2. On the seeder, you can take advantage of the nesting features of Prisma so that you don't have to manually link the records. That way, you don't have to worry about ids.

schema.prisma suggestion

model User {
  id       Int       @id @default(autoincrement())
  email    String    @unique
  name     String?
  accounts Account[]
}

model Account {
  id        Int      @id @unique @default(autoincrement())
  email     String
  bank      String
  users     User[]
  createdAt DateTime @default(now())
}

seed.js suggestion

const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();

async function main() {
    const usersData = [
        {
            email: "[email protected]",
            name: "Pranit1",
            banks: ["VCB", "ACB"],
        },
        {
            email: "[email protected]",
            name: "Pranit3",
            banks: ["VCB"],
        },
    ];

    const users = await prisma.$transaction(
        usersData.map((user) =>
            prisma.user.upsert({
                where: { email: user.email },
                update: {},
                create: {
                    name: user.name,
                    email: user.email,
                    accounts: {
                        create: user.banks.map((bank) => ({
                            email: user.email,
                            bank,
                        })),
                    },
                },
            })
        )
    );
}

main()
    .catch((e) => {
        console.error(e);
        process.exit(1);
    })
    .finally(async () => {
        await prisma.$disconnect();
    });

Terminate

Thank you for your solution. I was using Mysql, though I dont see how a different database would give different results for a seemingly basic model. – Relapse

You're welcome! I agree with you, was just keeping my mind open to possible bugs in a specific use case – Terminate

I got this error I want to migrate data from other source to seed into new database and i dump data into json and provide it to prisma seeding. But now if i am adding new record getting this error. How can i handle this case. – Daryn

Answer 2

I had a similar issue because map does not wait for promises to resolve. Instead I had to replace it with a normal for loop.

So if there was already a value it wont attempt to create a new value again (instead update it using upsert)

Galbanum

Answer 3

Another way to fix it is editing the sequence after the seeding. You can do it by running the command: await prisma.$queryRaw`ALTER SEQUENCE "Member_id_seq" RESTART WITH 100;

Treadmill

Answer 4

I had the same issue, I solved it by adding @unique to my scheme in the id column.

Zeitgeist

Answer 5

This may be a sequence issue. Auto increment ID need to take to the last record of your table. Then your issue will be resolved.

To do that execute same Failed request again and again until reach to the last record. When you reach to the last id, your upcoming requests will be saved as usual

for the best result first reset sequence table and then reset auto increment. this will reset auto increment id and sequence again start from 1

Reset the sequence:

ALTER SEQUENCE tablename_id_seq RESTART;

Then update the table's ID column:

UPDATE tablename SET id = DEFAULT;

Cataphoresis

Answer 6

I suspect this is an issue regarding how upsert handles SEQUENCES and one would have to use a traditional create

Frerichs