# Junction Tables (Many-to-Many)

> Model many-to-many relationships in Schemity with junction tables. Schemity auto-adds the foreign keys and composite primary key and detects the join table for you.

Source: https://schemity.com/doc/auto-junction-tables/

A relational database cannot store a **many-to-many** relationship directly - it needs a junction table (also called a join or link table) in the middle. Schemity does the fiddly parts for you, so an **auto junction table** is mostly automatic even though you place it deliberately.

Nothing installed yet? [Schemity Lite](https://schemity.com/lite/) is the free browser version and generates junction tables the same way, so you can follow the steps below before deciding on the desktop app.

## Why do many-to-many relationships need a junction table?

Say students and courses have a many-to-many relationship. You cannot put a foreign key on either side alone. You need a `students_courses` entity holding `student_id` and `course_id`, with both forming a composite primary key.

This page covers how Schemity builds that table for you. For the concept itself - why the database cannot store N:N directly, and when the junction should take its own `id` instead of the composite key - see [Many-to-Many Relationships and Junction Tables](https://schemity.com/blog/many-to-many-shouldnt-mean-hand-building-the-junction-table/).

## How do I create a junction table?

1. Add a new **entity** to sit between the two parents, and leave it without fields.
2. Draw a relationship from the first parent to this entity, and another from the second parent to it.

As you draw those two relationships, Schemity:

- adds a **foreign key field** to the junction entity for each parent,
- makes the two foreign keys a **composite primary key**, so each pairing is unique, and
- **detects** the entity as a junction table (when all its fields are foreign keys or PK foreign keys) and gives it a sensible **auto-generated name** following your naming convention, for example `students_courses`.

## Can I edit the auto-generated junction table?

The junction entity is a normal entity. You can:

- Rename it to match your conventions.
- Add extra columns - a junction table often carries its own attributes, like `enrolled_at` or `role`.
- Add a [composite unique constraint](https://schemity.com/doc/check-constraints-composite-unique/) if the pairing needs extra scoping.

## Why does modeling N:N correctly matter?

Modeling N:N correctly is one of the most common places ERDs go wrong. Letting Schemity wire up the foreign keys and composite key keeps your **1:N vs N:N relationships** honest and your schema normalized.

## What if a junction table came from an existing database?

A junction table Schemity drew for you is correct by construction. One reverse-engineered from a live database, or reshaped by hand afterwards, may not be - and the failure is silent, because a link table with no uniqueness over its foreign key pair accepts the same pairing again and again. [Schema Lint](https://schemity.com/doc/schema-lint/) checks exactly that, and accepts both correct forms: a composite primary key over the pair, or a surrogate `id` plus a unique constraint on the pair. It also catches the uniqueness that covers the wrong columns, and the multi-column unique containing a nullable column - the one that looks right in a schema dump and enforces nothing.

## Next

Check the schema against the rules with [Schema Lint](https://schemity.com/doc/schema-lint/).