> ## Documentation Index
> Fetch the complete documentation index at: https://archie.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Relationships

> Relationships connect tables with foreign keys. Choose between one-to-one, one-to-many, and many-to-many.

A relationship connects two tables with a foreign key. When you add a [relationship field](/features/backend/data-model/fields/relationship), Archie creates the foreign key in the database, generates the inverse field on the other table automatically, and exposes both sides as nested fields in the GraphQL API.

This page covers the cardinalities you can model, how to set up many-to-many, and how the relationships look in the auto-generated APIs.

## Cardinality

| Cardinality      | What it means                                                   | Real-world example                                                |
| ---------------- | --------------------------------------------------------------- | ----------------------------------------------------------------- |
| **One-to-One**   | Record A ↔ exactly one Record B (and vice versa).               | A `User` has one `Profile`.                                       |
| **One-to-Many**  | Record A ↔ many Record Bs, but each B belongs to exactly one A. | An `Author` has many `Books`; each `Book` has one `Author`.       |
| **Many-to-Many** | Record A ↔ many Record Bs **and** Record B ↔ many Record As.    | A `Student` takes many `Courses`; a `Course` has many `Students`. |

The Relationship field type supports **One-to-One** and **One-to-Many** directly. Many-to-many is modeled with a join table — see below.

## One-to-many

The most common cardinality. The "many" side holds the foreign key.

**Example:** an `author` has many `books`. The `books` table holds an `author_id` relationship field pointing to `authors`.

When you add the `author` relationship on `books` with cardinality **One-to-Many** (one author, many books), Archie automatically:

* Creates the `author_id` foreign key column on `books`.
* Generates a `books` nested field on `Author` in the GraphQL schema.
* Generates an `author` nested field on `Book` in the GraphQL schema.

```graphql theme={null}
query {
  author(id: "...") {
    name
    books {
      title
    }
  }
}

query {
  book(id: "...") {
    title
    author {
      name
    }
  }
}
```

## One-to-one

A `user` has exactly one `profile`, and a `profile` belongs to exactly one `user`.

Add a relationship field on the side that owns the foreign key (typically `profile.user_id`), pick **One-to-One** as the cardinality, and Archie enforces the uniqueness so that no two profiles can point to the same user.

```graphql theme={null}
query {
  user(id: "...") {
    profile {
      avatar_url
    }
  }
}
```

## Many-to-many

For many-to-many, create a **join table** that holds two relationship fields — one to each side.

**Example:** students enrolling in courses.

1. Create a `students` table.
2. Create a `courses` table.
3. Create a `student_courses` join table with two fields:
   * `student` — relationship to `students`, cardinality **One-to-Many**.
   * `course` — relationship to `courses`, cardinality **One-to-Many**.
4. Optionally, add a unique [index](/features/backend/data-model/indexes) across `(student, course)` to prevent duplicate enrollments.

The auto-generated GraphQL API now lets you traverse from a student to all their courses (via the join table) and from a course to all its students.

```graphql theme={null}
query {
  student(id: "...") {
    name
    student_courses {
      course {
        title
      }
    }
  }
}
```

The join table itself is a first-class table — you can attach extra fields to it (for example `enrolled_at`, `grade`) to record per-link metadata.

## Mandatory vs. optional relationships

Toggle **Mandatory** on the relationship field to require a related record. A non-mandatory relationship can be `null` — for example, a `comment` can optionally belong to a `parent_comment` for threaded replies.

## Cascade behavior on delete

By default, the database prevents you from deleting a record that other records reference — this protects against orphaned data. If you have an `author` with `books`, deleting the author fails until the books are reassigned or deleted.

Common alternatives:

| Behavior               | What it does                                   | When to use it                                                                          |
| ---------------------- | ---------------------------------------------- | --------------------------------------------------------------------------------------- |
| **Restrict** (default) | Block the delete while related records exist.  | Default. Forces you to handle related data explicitly.                                  |
| **Cascade**            | Delete related records too.                    | Strong ownership — deleting a `post` should delete its `comments`.                      |
| **Set null**           | Clear the reference; keep the related records. | Loose ownership — deleting a `category` shouldn't delete its `posts`, just unlink them. |

Configure cascade behavior through the relationship field's configuration panel.

## How it appears in the API

Every relationship is exposed in the auto-generated GraphQL API as a nested field on each side. Filtering, sorting, and pagination on related collections is supported. See the [GraphQL API Explorer](/features/backend/graphql-api-explorer/overview) to inspect the exact shape generated for your tables. The [REST API](/features/backend/rest-api-explorer/overview) exposes related records via standard endpoint patterns and query parameters.

## Permissions

Reading or writing a relationship requires permissions on **both** sides. A user can only see related records on a table they're allowed to read. Configure this in [Role-Based Access](/features/backend/app-services/role-based-access).

## FAQ

<AccordionGroup>
  <Accordion title="Why is there no Many-to-Many option in the relationship field?">
    Many-to-many is always implemented under the hood as a join table with two one-to-many relationships. Modeling it explicitly gives you a place to attach metadata to each link (for example, when a student enrolled, what grade they got). The Data Model exposes that pattern directly rather than hiding it.
  </Accordion>

  <Accordion title="Do I need to manually add a foreign key column?">
    No. The relationship field is the foreign key. Adding it creates the column, the database constraint, and the inverse query in GraphQL all at once.
  </Accordion>

  <Accordion title="What happens to the inverse if I rename a relationship field?">
    The inverse field updates to match the new name. Any client code that referenced the old nested field name needs to be updated.
  </Accordion>

  <Accordion title="Can I have multiple relationships between the same two tables?">
    Yes. For example, a `messages` table could have both a `sender` and a `recipient` relationship pointing to `users`. Pick distinct field names so the two relationships are clearly distinguishable in the GraphQL schema.
  </Accordion>

  <Accordion title="What does cascade delete look like in practice?">
    When you delete a `post` with `comments` configured to cascade, the database deletes the post and every comment that points to it as part of the same transaction. Choose cascade only when child records have no meaning without the parent.
  </Accordion>
</AccordionGroup>