Migrate: Self relation one-to-many can not insert data

[lglie]

model User {
  id       Int     @id
  name     String?
  parent   User?   @relation("parentChildren", fields: [parentId], references: [id])
  parentId String?
  children User[]  @relation("parentChildren")
}

When inserting the second data to the same parent node, the following error message appears:

Unique constraint failed on the fields: (`parentId`)
    at PrismaClientFetcher.request {
  code: 'P2002',
  meta: { target: [ 'parentId' ] }
}

[asciant]

It appears that the type of parentId doesn't match that of the id field it refers to:

model User {
  id Int @id // <-- This is an Int
  name String?
  parent User? @relation("parentChildren", fields: [parentId], references: [id])
  parentId String? // <-- This is a string
  children User[] @relation("parentChildren")
}

Does that fix your issue?

[lglie]

Still error：

------------------ 原始邮件 ------------------
发件人: "Haiden Taylor"<[email protected]>;
发送时间: 2020年4月7日(星期二) 凌晨5:34
收件人: "prisma/prisma"<[email protected]>;
抄送: "廖国烈"<[email protected]>;"Author"<[email protected]>;
主题: Re: [prisma/prisma] Self relation one-to-many can not insert data (#2105)

It appears that the type of parentId doesn't match that of the id field it refers to:
model User { id Int @id // <-- This is an Int name String? parent User? @relation("parentChildren", fields: [parentId], references: [id]) parentId String? // <-- This is a string children User[] @relation("parentChildren") }
Does that fix your issue?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or unsubscribe.

[pantharshit00]

First of all match the scalars that hold the keys as suggested by @asciant:

model User {
  id       Int     @id
  name     String?
  parent   User?   @relation("parentChildren", fields: [parentId], references: [id])
  parentId Int?
  children User[]  @relation("parentChildren")
}

Now can you please share the code that is performing the insertion. I suspect that you are using the same id twice as Ids are managed by you which will fails as ids need to be unique.

[lglie]

// prisma.schema
generator ts {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-1.0.x"]
}

datasource sqlite {
  provider = "sqlite"
  url      = "sqlite:dev.db"
}

model User {
  id       Int  @id
  name     String
  parent   User?   @relation("parentChildren", fields: [parentId], references: [id])
  parentId Int?
  children User[]  @relation("parentChildren")
}

// seed.js
let { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
  await prisma.user.create({
    data: {
      id: 1,
      name: 'father',
    }
  });
  console.log(await prisma.user.findMany());

  await prisma.user.create({
    data: {
      id: 22,
      name: 'son1',
      parent: { connect: { id: 1 } },
    }
  });
  console.log(await prisma.user.findMany());

  await prisma.user.create({
    data: {
      id: 23,
      name: 'son2',
      parent: { connect: { id: 1 } },
    }
  });
  console.log(await prisma.user.findMany());
}

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

result:
[ { id: 1, name: 'father', parentId: null } ]
[
  { id: 1, name: 'father', parentId: null },
  { id: 22, name: 'son1', parentId: 1 }
]
PrismaClientKnownRequestError: 
Invalid `prisma.user.create()` invocation in
/home/ubuntu/app/learning/prisma/seed.js:22:21

Unique constraint failed on the fields: (`parentId`)
    at PrismaClientFetcher.request (/home/ubuntu/app/learning/node_modules/@prisma/client/runtime/index.js:1:51353)
    at processTicksAndRejections (internal/process/task_queues.js:94:5) {
  code: 'P2002',
  meta: { target: [ 'parentId' ] }
}

[mufasa71]

I have the same issue and found that it is caused by unique index:
Here is the schema:

model Category {
  createdAt      DateTime   @default(now())
  id             String     @id @default(cuid())
  name           String
  updatedAt      DateTime   @updatedAt
  parentId       String?
  parent         Category?  @relation("CategoryToParent", fields: [parentId], references: [id])
  children       Category[] @relation("CategoryToParent")
}

here is sql query i trying to do:

COPY public."Category" ("createdAt", id, name, "parentId", "updatedAt") FROM stdin;
2019-04-21 18:33:26.128 cjur9s6mu004c0741aqf2e4en   Biotech/Biopharma   \N  2019-04-21 18:33:26.128
2019-04-21 18:33:26.644 cjur9s705004h0741yw6vs1od   Real Estate \N  2019-04-21 18:33:26.644
2019-04-21 18:33:26.993 cjur9s79t004m0741b3xru9y0   Research & Development  \N  2019-04-21 18:33:26.993
2019-04-21 18:33:50.32  cjur9sp9t00c40741udw4q1x7   Advertising/Marketing/PR    \N  2019-04-21 18:33:50.32
2019-04-21 18:33:27.374 cjur9s7kf004r0741pdwfjwpp   Other   cjur9s79t004m0741b3xru9y0   2019-04-21 18:33:27.374
2019-04-21 18:33:28.22  cjur9s87w00510741wvcmo9nj   Consulting  \N  2019-04-21 18:33:28.22
2019-04-21 18:33:28.564 cjur9s8hg005607417qlm7ltz   Analytics/Testing Labs  \N  2019-04-21 18:33:28.564
2019-04-21 18:33:28.959 cjur9s8sf005b0741blg27yny   Holding Company \N  2019-04-21 18:33:28.959
2019-04-21 18:33:29.307 cjur9s923005g0741ami9ohxw   Packaging/Labeling  \N  2019-04-21 18:33:29.307
2019-04-21 18:33:29.729 cjur9s9dt005l0741jmz4up1y   Manufacturer    \N  2019-04-21 18:33:29.729
2019-04-21 18:33:30.124 cjur9s9os005q07415qzcp17d   Other   cjur9s9dt005l0741jmz4up1y   2019-04-21 18:33:30.124
2019-04-21 18:33:31.046 cjur9saee00600741r1599cte   Software    \N  2019-04-21 18:33:31.046
2019-04-21 18:34:00.336 cjur9sx0000fg0741wt8565my   Oils/Concentrates   cjur9swpg00fb07417wxtkft5   2019-04-21 18:34:00.336
2019-04-21 18:33:31.432 cjur9sap400650741t00u0bnm   Compliance  cjur9saee00600741r1599cte   2019-04-21 18:33:31.432
2019-04-21 18:33:50.706 cjur9spki00c907418jcwm5gh   Devices cjur9s9dt005l0741jmz4up1y   2019-04-21 18:33:50.706
2019-04-21 18:33:32.318 cjur9sbdq006f0741tlsx14v9   Cultivation Supplies    cjur9s9dt005l0741jmz4up1y   2019-04-21 18:33:32.318
2019-04-21 18:33:51.552 cjur9sq8000cj074189d8a070   Processing  \N  2019-04-21 18:33:51.552
2019-04-21 18:33:33.155 cjur9sc0z006p0741kp55dixd   Cultivation Equipment   cjur9s9dt005l0741jmz4up1y   2019-04-21 18:33:33.155
2019-04-21 18:33:33.992 cjur9sco9006z07419hjee03h   Cultivation Management  cjur9saee00600741r1599cte   2019-04-21 18:33:33.992
2019-04-21 18:34:01.215 cjur9sxof00fq07418e8knu63   Security    \N  2019-04-21 18:34:01.215
2019-04-21 18:33:34.875 cjur9sdcr00790741yv1knzu6   Financing   cjur9s705004h0741yw6vs1od   2019-04-21 18:33:34.875
2019-04-21 18:33:35.713 cjur9se02007j0741ok0yve2a   Financial Services  \N  2019-04-21 18:33:35.713
2019-04-21 18:33:51.934 cjur9sqim00co0741dv9u9i9u   Oils/Concentrates   cjur9sq8000cj074189d8a070   2019-04-21 18:33:51.934
2019-04-21 18:33:36.133 cjur9sebq007o074108543axx   Private Equity/Venture Capital  cjur9se02007j0741ok0yve2a   2019-04-21 18:33:36.133
2019-04-21 18:33:36.984 cjur9sezc007y0741mu6v9ft3   Other   cjur9s87w00510741wvcmo9nj   2019-04-21 18:33:36.984
2019-04-21 18:33:37.831 cjur9sfmw00880741hv2i0n16   Insurance   cjur9se02007j0741ok0yve2a   2019-04-21 18:33:37.831
2019-04-21 18:33:52.775 cjur9sr6000cy07414uqx4q4m   Oils/Concentrates   cjur9s9dt005l0741jmz4up1y   2019-04-21 18:33:52.775
\.

--

as you can see cjur9s9dt005l0741jmz4up1y appears multiple times as parent id, so in result:

psql:Category.sql:74: ERROR:  duplicate key value violates unique constraint Category_parentId DETAIL:  Key (parentId)=(cjur9s9dt005l0741jmz4up1y) already exists. 

here is how it looks like in db:

[jamespotz]

I also had encountered this issue, what fixed it for me was rearranging the children and parent.

model User {
  id       Int     @id
  name     String?
  children User[] @relation("parentChildren") 
  parent   User?   @relation("parentChildren", fields: [parentId], references: [id])
  parentId Int?
}

[mufasa71]

Confirm, ordering resolves this issue

[pantharshit00]

Hi all,

I was able to reproduce this with 2.0.0-beta.1 that ordering of the fields was creating a unique index but I was not able to reproduce this with 2.0.0-beta.2

So please try again with the latest version. Ordering should not matter here.

[squirly]

I was previously having this issue with 2.0.0-beta.1 and reordering fixed it.

I just upgraded to 2.0.0-beta.2, reverted the reordering, and everything is working as it should. I would say that this bug has been fixed!

[pantharshit00]

I am waiting for @lglie to respond before closing the issue

[lglie]

In later versions, this has not happened

------------------ 原始邮件 ------------------
发件人: "Harshit Pant"<[email protected]>;
发送时间: 2020年4月21日(星期二) 晚上8:02
收件人: "prisma/migrate"<[email protected]>;
抄送: "廖国烈"<[email protected]>;"Author"<[email protected]>;
主题: Re: [prisma/migrate] Self relation one-to-many can not insert data (#422)

I am waiting for @Iglie to respond before closing the issue

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or unsubscribe.

[pantharshit00]

Awesome, so I am going to close this now