Getting Started with SQLAlchemy — Composite Unique, Group By, Order By (Part 9)

In this tutorial, we will walk through the steps to create a composite unique constraint, and how to use the GROUP BY and ORDER BY.

Unique constraint

This is useful when we want to create groups of unique combinations. We have some products in the app and want to group them into watchlists. We want to disallow a user from having multiple watchlists with the same name, but other users can also have watchlists with the same name.

We will start by defining two tables: user and watchlist.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Text, ForeignKey, UniqueConstraint

metadata = MetaData()

user_table = Table("user", metadata,
    Column("id", Integer, primary_key=True),
    Column("first_name", Text, nullable=False),
    Column("last_name", Text, nullable=False),
)

watchlist_table = Table("watchlist", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", Text, nullable=False),
    Column("user_id", ForeignKey(user_table.c.id, ondelete="CASCADE"), nullable=False),
    UniqueConstraint("user_id", "name", name="user_product_watchlist_unq"),
)


metadata.drop_all(engine)
metadata.create_all(engine)