# Troubleshooting relations (/docs/orm/prisma-schema/data-model/relations/troubleshooting-relations)

Location: ORM > Prisma Schema > Data Model > Relations > Troubleshooting relations

Modelling your schema can sometimes offer up some unexpected results. This section aims to cover the most prominent of those.

Implicit many-to-many self-relations return incorrect data if order of relation fields change [#implicit-many-to-many-self-relations-return-incorrect-data-if-order-of-relation-fields-change]

Problem [#problem]

In the following implicit many-to-many self-relation, the lexicographic order of relation fields in `a_eats` (1) and `b_eatenBy` (2):

```prisma highlight=4,5;normal
model Animal {
  id        Int      @id @default(autoincrement())
  name      String
  a_eats    Animal[] @relation(name: "FoodChain") // [!code highlight]
  b_eatenBy Animal[] @relation(name: "FoodChain") // [!code highlight]
}
```

The resulting relation table in SQL looks as follows, where `A` represents prey (`a_eats`) and `B` represents predators (`b_eatenBy`):

| A            | B          |
| :----------- | :--------- |
| 8 (Plankton) | 7 (Salmon) |
| 7 (Salmon)   | 9 (Bear)   |

The following query returns a salmon's prey and predators:

```ts
const getAnimals = await prisma.animal.findMany({
  where: {
    name: "Salmon",
  },
  include: {
    b_eats: true,
    a_eatenBy: true,
  },
});
```

```json
{
  "id": 7,
  "name": "Salmon",
  "b_eats": [
    {
      "id": 8,
      "name": "Plankton"
    }
  ],
  "a_eatenBy": [
    {
      "id": 9,
      "name": "Bear"
    }
  ]
}
```

Now change the order of the relation fields:

```prisma highlight=4,5;normal
model Animal {
  id        Int      @id @default(autoincrement())
  name      String
  b_eats    Animal[] @relation(name: "FoodChain") // [!code highlight]
  a_eatenBy Animal[] @relation(name: "FoodChain") // [!code highlight]
}
```

Migrate your changes and re-generate Prisma Client. When you run the same query with the updated field names, Prisma Client returns incorrect data (salmon now eats bears and gets eaten by plankton):

```ts
const getAnimals = await prisma.animal.findMany({
  where: {
    name: "Salmon",
  },
  include: {
    b_eats: true,
    a_eatenBy: true,
  },
});
```

```json
{
  "id": 1,
  "name": "Salmon",
  "b_eats": [
    {
      "id": 3,
      "name": "Bear"
    }
  ],
  "a_eatenBy": [
    {
      "id": 2,
      "name": "Plankton"
    }
  ]
}
```

Although the lexicographic order of the relation fields in the Prisma schema changed, columns `A` and `B` in the database **did not change** (they were not renamed and data was not moved). Therefore, `A` now represents predators (`a_eatenBy`) and `B` represents prey (`b_eats`):

| A            | B          |
| :----------- | :--------- |
| 8 (Plankton) | 7 (Salmon) |
| 7 (Salmon)   | 9 (Bear)   |

Solution [#solution]

If you rename relation fields in an implicit many-to-many self-relations, make sure that you maintain the alphabetic order of the fields - for example, by prefixing with `a_` and `b_`.

How to use a relation table with a many-to-many relationship [#how-to-use-a-relation-table-with-a-many-to-many-relationship]

There are a couple of ways to define an m-n relationship, implicitly or explicitly. Implicitly means letting Prisma ORM handle the relation table (JOIN table) under the hood, all you have to do is define an array/list for the non scalar types on each model, see [implicit many-to-many relations](/orm/prisma-schema/data-model/relations/many-to-many-relations#implicit-many-to-many-relations).

Where you might run into trouble is when creating an [explicit m-n relationship](/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations), that is, to create and handle the relation table yourself. **It can be overlooked that Prisma ORM requires both sides of the relation to be present**.

Take the following example, here a relation table is created to act as the JOIN between the `Post` and `Category` tables. This will not work however as the relation table (`PostCategories`) must form a 1-to-many relationship with the other two models respectively.

The back relation fields are missing from the `Post` to `PostCategories` and `Category` to `PostCategories` models.

```prisma
// This example schema shows how NOT to define an explicit m-n relation

model Post {
  id             Int              @id @default(autoincrement())
  title          String
  categories     Category[] // This should refer to PostCategories
}

model PostCategories {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int
  @@id([postId, categoryId])
}

model Category {
  id             Int              @id @default(autoincrement())
  name           String
  posts          Post[] // This should refer to PostCategories
}
```

To fix this the `Post` model needs to have a many relation field defined with the relation table `PostCategories`. The same applies to the `Category` model.

This is because the relation model forms a 1-to-many relationship with the other two models its joining.

```prisma highlight=5,21;add|4,20;delete
model Post {
  id             Int              @id @default(autoincrement())
  title          String
  categories     Category[] // [!code --]
  postCategories PostCategories[] // [!code ++]
}

model PostCategories {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int

  @@id([postId, categoryId])
}

model Category {
  id             Int              @id @default(autoincrement())
  name           String
  posts          Post[] // [!code --]
  postCategories PostCategories[] // [!code ++]
}
```

Using the @relation attribute with a many-to-many relationship [#using-the-relation-attribute-with-a-many-to-many-relationship]

It might seem logical to add a `@relation("Post")` annotation to a relation field on your model when composing an implicit many-to-many relationship.

```prisma
model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[] @relation("Category")
  Category   Category?  @relation("Post", fields: [categoryId], references: [id])
  categoryId Int?
}

model Category {
  id     Int    @id @default(autoincrement())
  name   String
  posts  Post[] @relation("Post")
  Post   Post?  @relation("Category", fields: [postId], references: [id])
  postId Int?
}
```

This however tells Prisma ORM to expect **two** separate one-to-many relationships. See [disambiguating relations](/orm/prisma-schema/data-model/relations#disambiguating-relations) for more information on using the `@relation` attribute.

The following example is the correct way to define an implicit many-to-many relationship.

```prisma highlight=4,11;delete|5,12;add
model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[] @relation("Category") // [!code --]
  categories Category[] // [!code ++]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[] @relation("Post") // [!code --]
  posts Post[] // [!code ++]
}
```

The `@relation` annotation can also be used to [name the underlying relation table](/orm/prisma-schema/data-model/relations/many-to-many-relations#configuring-relation-table-name) created on a implicit many-to-many relationship.

```prisma
model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[] @relation("CategoryPostRelation")
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[] @relation("CategoryPostRelation")
}
```

Using m-n relations in databases with enforced primary keys [#using-m-n-relations-in-databases-with-enforced-primary-keys]

Problem [#problem-1]

Some cloud providers enforce the existence of primary keys in all tables. However, any relation tables (JOIN tables) created by Prisma ORM (expressed via `@relation`) for many-to-many relations using implicit syntax do not have primary keys.

Solution [#solution-1]

You need to use [explicit relation syntax](/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations), manually create the join model, and verify that this join model has a primary key.

## Related pages

- [`Many-to-many relations`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations): How to define and work with many-to-many relations in Prisma.
- [`One-to-many relations`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/one-to-many-relations): How to define and work with one-to-many relations in Prisma.
- [`One-to-one relations`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/one-to-one-relations): How to define and work with one-to-one relations in Prisma.
- [`Referential actions`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/referential-actions): Referential actions let you define the update and delete behavior of related models on the database level
- [`Relation mode`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/relation-mode): Manage relations between records with relation modes in Prisma