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

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

• javascript
• mysql
• prisma

• What kind of database are you using? Tried to reproduce but it works fine for me using Postgres – Victor Iris Commented Nov 10, 2021 at 2:47

6 Answers 6

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();
    });

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)

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;

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

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

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;