Python SQLAlchemy Primary Key, Foreign Key, and Relationships

Defining primary keys and foreign keys in SQLAlchemy gives you a correct database schema, but it does not by itself give you the relationship attributes you navigate in Python. A foreign key constraint and a relationship() declaration are separate concerns: the constraint tells the database how rows relate, while relationship() tells the ORM how to expose that relation on your model objects. Understanding how python sqlalchemy primary key foreign key and relationships fit together is what separates a schema that merely works from one that is pleasant to query and safe to modify.

How Primary Keys, Foreign Keys, and Relationships Fit Together

SQLAlchemy builds ORM relationships on top of the foreign keys you declare in your column definitions. The primary_key=True flag marks the identity column of a table. A ForeignKey("authors.id") creates a database-level constraint that references that primary key. A relationship() declaration then uses the foreign key metadata to construct the join condition between the two mapped classes.

The three layers are distinct:

• mapped_column(primary_key=True) defines the identity column.
• ForeignKey("authors.id") defines the referential constraint.
• relationship() defines the Python-side navigation attribute.

You can have a foreign key without a relationship(), and you can even have a relationship() that does not rely on a foreign key by passing an explicit primaryjoin, but the common and recommended path is to declare the foreign key first and let relationship() infer the join.

Declaring Primary Keys in SQLAlchemy Models

A primary key in SQLAlchemy is declared with primary_key=True on a column:

from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

For an integer primary key, SQLAlchemy uses an auto-incrementing sequence on most databases, so you do not need to supply a value when inserting. If you need a composite primary key, declare multiple columns with primary_key=True:

class Membership(Base):
    __tablename__ = "memberships"

    user_id: Mapped[int] = mapped_column(primary_key=True)
    group_id: Mapped[int] = mapped_column(primary_key=True)

Composite keys are valid, but they add complexity to relationships. A relationship() that references a table with a composite key must match all key columns, and the join conditions become harder to read. Prefer a single surrogate integer key unless the domain genuinely requires a composite natural key.

Declaring Foreign Keys

A foreign key column references a primary key (or unique column) in another table:

from sqlalchemy import ForeignKey


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"), nullable=False)
    title: Mapped[str] = mapped_column(String(200))

The ForeignKey argument is a string in the form "table.column", where table is the __tablename__ of the target, not the Python class name. This matters when your table name differs from the model name.

Setting nullable=False enforces that every book must belong to an author at the database level. If you allow a book to exist without an author, leave the column nullable.

The ondelete parameter controls what happens when the referenced row is deleted:

author_id: Mapped[int] = mapped_column(
    ForeignKey("authors.id", ondelete="CASCADE"), nullable=False
)

ondelete="CASCADE" tells the database to delete dependent rows automatically. ondelete="SET NULL" requires a nullable column and sets it to NULL. These are database-level behaviors; the ORM still needs configuration to match them, which is covered later in this article.

Defining One-to-Many and Many-to-One Relationships

With the foreign key in place, you can add relationship() on both sides:

class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    books: Mapped[list["Book"]] = relationship(back_populates="author")


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"), nullable=False)
    title: Mapped[str] = mapped_column(String(200))

    author: Mapped["Author"] = relationship(back_populates="books")

The "one" side (Author) gets a collection attribute typed as Mapped[list["Book"]]. The "many" side (Book) gets a scalar attribute typed as Mapped["Author"]. The back_populates argument on each side names the attribute on the other side, which keeps the two relationships in sync.

Once both sides are declared, you can navigate in either direction:

author = session.get(Author, 1)
print(author.books)          # collection of Book instances

book = session.get(Book, 10)
print(book.author.name)      # the related Author instance

If you only need one-directional navigation, you can declare relationship() on a single side without back_populates. The relationship still works, but you lose the convenience of the reverse attribute.

Many-to-Many Relationships with an Association Table

A many-to-many relationship requires an association table that holds foreign keys to both sides:

from sqlalchemy import Column, Table

book_authors = Table(
    "book_authors",
    Base.metadata,
    Column("book_id", ForeignKey("books.id"), primary_key=True),
    Column("author_id", ForeignKey("authors.id"), primary_key=True),
)


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))

    authors: Mapped[list["Author"]] = relationship(
        secondary=book_authors, back_populates="books"
    )


class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    books: Mapped[list["Book"]] = relationship(
        secondary=book_authors, back_populates="authors"
    )

The secondary argument names the association table. Both sides reference the same table, and SQLAlchemy uses the two foreign keys in it to build the join. The association table's composite primary key prevents duplicate pairs.

When you add or remove items from either collection, SQLAlchemy inserts or deletes rows in the association table automatically:

book.authors.append(author)
session.commit()  # inserts into book_authors

If the association table needs extra columns, such as a created_at timestamp, a plain Table is not enough. You need to promote it to a mapped model and use two one-to-many relationships through that model. That pattern is more verbose but gives you full control over the association data.

Choosing Between backref and back_populates

backref is the older shorthand that creates the reverse relationship implicitly:

class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    books: Mapped[list["Book"]] = relationship(backref="author")

With backref, you do not declare author on Book; SQLAlchemy adds it for you. This is compact, but it hides the reverse side from the model definition, which makes the relationship harder to discover when reading the Book class.

back_populates requires both sides to be declared explicitly:

class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    books: Mapped[list["Book"]] = relationship(back_populates="author")


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"), nullable=False)
    title: Mapped[str] = mapped_column(String(200))

    author: Mapped["Author"] = relationship(back_populates="books")

For a codebase where relationships are central to the domain model, back_populates is the clearer choice. The explicit declaration makes both sides visible in their respective classes and avoids surprises when you rename an attribute.

Avoiding the N+1 Query Problem

By default, SQLAlchemy loads relationships lazily. Accessing author.books issues a new SELECT statement at that moment. In a loop over many authors, this produces the N+1 query pattern:

authors = session.scalars(select(Author)).all()

for author in authors:
    print(author.name, [book.title for book in author.books])  # one query per author

Each iteration triggers an additional query for the books collection. With 100 authors, that is 101 queries total.

Use eager loading to fetch the related rows in a controlled way:

from sqlalchemy import select
from sqlalchemy.orm import selectinload

stmt = select(Author).options(selectinload(Author.books))
authors = session.scalars(stmt).all()

for author in authors:
    print(author.name, [book.title for book in author.books])

selectinload issues a second query that loads all related books with an IN clause, then populates the collections in memory. joinedload is the alternative that uses a JOIN in the original query. For collections, selectinload is usually the better choice because a JOIN can multiply the number of rows returned when the collection is large, and the ORM has to deduplicate the results.

Eager loading is a query-time decision, not a model definition. You can leave the default lazy behavior in the model and opt into eager loading per query, which keeps the model definition simple and gives you control over which queries need the related data.

Controlling Deletion Behavior with Cascade

The default behavior when you delete an Author depends on the database constraint and the ORM cascade settings. Without any cascade configuration, SQLAlchemy will try to set the foreign key column to NULL on related rows, which fails if the column is nullable=False.

The cascade parameter on relationship() controls ORM-level behavior:

class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    books: Mapped[list["Book"]] = relationship(
        back_populates="author", cascade="all, delete-orphan"
    )

With cascade="all, delete-orphan", deleting an Author deletes all its books through the ORM. The delete-orphan part also deletes a book when it is removed from the author.books collection, even if the book object still exists in the session:

author.books.remove(book)
session.commit()  # book is deleted from the database

If you prefer to let the database handle deletion, combine ondelete="CASCADE" on the foreign key with passive_deletes=True on the relationship:

class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(
        ForeignKey("authors.id", ondelete="CASCADE"), nullable=False
    )
    title: Mapped[str] = mapped_column(String(200))


class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    books: Mapped[list["Book"]] = relationship(
        back_populates="author", passive_deletes=True
    )

With passive_deletes=True, the ORM skips loading and deleting the related rows itself and relies on the database's ON DELETE CASCADE. This avoids an extra SELECT to load the children before deletion, which matters when an author has thousands of books. The tradeoff is that the ORM no longer knows about the deleted children in the current session, so stale in-memory objects may remain until the session is refreshed.

The right choice depends on whether you want the ORM to own the deletion logic or delegate it to the database. ORM-level cascade is more portable across databases and keeps behavior visible in the model. Database-level cascade is more efficient for large collections and keeps the rule in the schema.