统一数据库命名与约束

当文章数据还在 Python 列表里时，重复邮箱、无效作者 ID 和负数阅读时长都只能靠代码分支拦截。一旦有两个进程同时写入，应用层的“先查询再插入”就可能在检查和写入之间失效。数据库约束属于最终写入边界，应该和表结构一起设计，并且给每个约束一个能在错误日志和迁移文件中找到的名字。

本节为贯穿案例建立 users 和 posts 两张表。示例选择小写蛇形命名和复数表名，是本教程的团队约定；单数表名也可以成立，关键是同一项目持续使用一种规则。时间字段使用 _at 后缀，作者关系统一使用 author_id，避免在同一关系中混用 creator_id、profile_id 等含义不同的名字。

先设计最小关系和约束

案例中的约束分工如下：

对象	约束	要保证的事实
users.id、posts.id	主键	每行都有稳定的唯一标识，且不能为 NULL
users.email、posts.slug	唯一约束	同一表中不能出现重复值
posts.author_id	外键	文章作者必须存在于 users
posts.title	检查约束	标题不能是空字符串
posts.reading_time_minutes	检查约束	阅读时长在 1 到 120 分钟之间
posts.author_id, posts.created_at	索引	支持作者文章列表按时间查询

SQLAlchemy 2 的声明式映射使用 DeclarativeBase、Mapped 和 mapped_column。MetaData.naming_convention 会在 Python 构造约束时生成名称，Alembic 后续生成候选迁移时也能沿用这些名称。ck 模板使用了 %(constraint_name)s，所以每个 CheckConstraint 必须显式给出名字；省略名字会在模型导入阶段报错，而不是等数据库建表时才发现。

<!-- file: ch05_schema/models.py -->

from __future__ import annotations


from datetime import datetime


from sqlalchemy import (
    CheckConstraint,
    DateTime,
    ForeignKey,
    Index,
    MetaData,
    String,
    Text,
)
from sqlalchemy.dialects import postgresql
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.schema import CreateIndex, CreateTable




NAMING_CONVENTION = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}




class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING_CONVENTION)




class User(Base):
    __tablename__ = "users"


    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(320), nullable=False, unique=True)
    display_name: Mapped[str] = mapped_column(
        String(80), nullable=False, index=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )




class Post(Base):
    __tablename__ = "posts"
    __table_args__ = (
        CheckConstraint("length(title) > 0", name="title_not_empty"),
        CheckConstraint(
            "reading_time_minutes BETWEEN 1 AND 120",
            name="reading_time_minutes_range",
        ),
        Index("ix_posts_author_created_at", "author_id", "created_at"),
    )


    id: Mapped[int] = mapped_column(primary_key=True)
    slug: Mapped[str] = mapped_column(String(160), nullable=False, unique=True)
    title: Mapped[str] = mapped_column(String(120), nullable=False)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    reading_time_minutes: Mapped[int] = mapped_column(nullable=False)
    author_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="RESTRICT"), nullable=False
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )




def self_check() -> None:
    users = Base.metadata.tables["users"]
    posts = Base.metadata.tables["posts"]
    assert set(Base.metadata.tables) == {"users", "posts"}


    user_constraints = {constraint.name for constraint in users.constraints}
    post_constraints = {constraint.name for constraint in posts.constraints}
    assert "pk_users" in user_constraints
    assert "uq_users_email" in user_constraints
    assert "pk_posts" in post_constraints
    assert "uq_posts_slug" in post_constraints
    assert "ck_posts_title_not_empty" in post_constraints
    assert "ck_posts_reading_time_minutes_range" in post_constraints
    assert "fk_posts_author_id_users" in post_constraints


    dialect = postgresql.dialect()
    ddl = "\n".join(
        str(CreateTable(table).compile(dialect=dialect))
        for table in Base.metadata.sorted_tables
    )
    indexes = "\n".join(
        str(CreateIndex(index).compile(dialect=dialect))
        for table in Base.metadata.sorted_tables
        for index in table.indexes
    )
    assert "CONSTRAINT pk_users PRIMARY KEY" in ddl
    assert "CONSTRAINT fk_posts_author_id_users" in ddl
    assert "CREATE INDEX ix_posts_author_created_at" in indexes
    assert "CREATE INDEX ix_users_display_name" in indexes
    print("models self-check passed; PostgreSQL DDL compiled without a database")




if __name__ == "__main__":
    self_check()

在已经安装 SQLAlchemy 的环境中运行：

python ch05_schema/models.py

这个检查只使用 PostgreSQL 方言编译 DDL，并没有连接 PostgreSQL，也没有真正创建表。它能发现模型导入、命名约定和生成 SQL 的问题；约束是否由服务器执行，仍需在隔离的 PostgreSQL 数据库中运行迁移和失败案例。

为什么不能只在应用层预检查

应用层可以先给用户更友好的提示，但它不能代替数据库约束。例如两个并发请求同时查询“这个 slug 是否存在”，两次查询都可能得到不存在，随后同时尝试插入。唯一约束会让其中一个写入成功，另一个收到数据库冲突；服务层应该捕获这个冲突并转换成约定的 409，而不是把预检查当成保证。

外键和检查约束也有同样的边界。posts.author_id=999 在应用层看似可以先查作者，但另一个事务可能在检查后删除作者；外键仍然是最终关系完整性边界。reading_time_minutes=0 可以在 Pydantic 中提前拒绝，但数据库约束保证任何写入路径（接口、脚本、后台任务）都不能绕过这个规则。

在独立的 PostgreSQL 练习数据库中，可以用以下语句观察三类失败。先通过正常迁移创建表并插入一名作者，再逐条执行；每条语句的约束名会出现在 PostgreSQL 错误中：

<!-- file: ch05_schema/constraint_failures.sql -->

-- 重复值：SQLSTATE 23505，命中 uq_users_email 或 uq_posts_slug
INSERT INTO users (id, email, display_name, created_at)
VALUES (2, 'alice@example.com', '另一个 Alice', CURRENT_TIMESTAMP);


-- 无效引用：SQLSTATE 23503，命中 fk_posts_author_id_users
INSERT INTO posts (
    id, slug, title, content, reading_time_minutes, author_id,
    created_at, updated_at
)
VALUES (
    2, 'missing-author', '无效作者', '正文', 5, 999,
    CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
);


-- 检查失败：SQLSTATE 23514，命中 ck_posts_reading_time_minutes_range
INSERT INTO posts (
    id, slug, title, content, reading_time_minutes, author_id,
    created_at, updated_at
)
VALUES (
    3, 'bad-reading-time', '错误时长', '正文', 0, 1,
    CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
);

不要在真实业务库中直接执行这些失败语句。数据库约束名称、SQLSTATE 和错误映射应在测试中固定，正文示例只说明需要验证的事实，本地的模型自检不应被描述成已经完成了 PostgreSQL 集成验证。

约束名称要服务于迁移和排错

命名约定不是为了让每个名字都更长，而是为了让错误和迁移可定位。uq_posts_slug 一眼能说明表和字段，fk_posts_author_id_users 能说明关系方向，ck_posts_reading_time_minutes_range 能提示失败条件。PostgreSQL 对标识符长度有上限，SQLAlchemy 会对过长的约定名称做确定性截断；团队仍应避免生成难以阅读的超长组合名称。

primary_key=True 同时表达主键唯一和非空语义；nullable=False 解决字段本身不可为空，但不会替代跨行唯一性或跨表引用。需要给多列组合设唯一性时，使用明确的 UniqueConstraint，不要分别给两列加 unique=True，因为两种约束的业务含义不同。

资料来源

• 主要参考：fastapi-best-practices 中文 README 的“设置数据库键命名约定”和“遵循命名规则”主题。本节采用其命名约定方向，但将表结构、约束和 PostgreSQL 失败案例改写为文章管理 API。
• 官方文档：SQLAlchemy 2.0 约束与索引、SQLAlchemy 2.0 元数据、PostgreSQL 约束。