在上一章，我们已经绘制好了博客系统的宏伟蓝图。现在，是时候戴上安全帽，化身“建筑工程师”，将图纸上的设计一砖一瓦地搭建成真实的数据库结构了。

这一章的目标非常明确：使用 CREATE TABLE 语句，将我们在上一章设计的所有表、列、主键、外键和约束，在 PostgreSQL 中完整地实现出来。

我们将把所有建表语句组织在一个 SQL 脚本文件中。这个文件将成为我们项目的“创世脚本”，任何一个新的开发者拿到它，都能一键创建出和我们一模一样的数据库环境。

27.1 创建数据库和 Schema

在创建表之前，我们先为我们的项目创建一个专属的数据库和一个专属的 Schema。这是一个非常好的实践，可以避免和服务器上的其他项目混淆。

第一步：创建数据库
在命令行（非 psql 环境）中执行：

createdb blog_system_db

或者在 psql 中执行：

CREATE DATABASE blog_system_db;

第二步：连接到新数据库

psql -d blog_system_db

或者在 psql 中：

\c blog_system_db

第三步：创建 Schema (可选，但推荐)
Schema 可以帮助我们更好地组织数据库内部的对象。我们可以为我们的博客应用创建一个名为 blog 的 schema。

CREATE SCHEMA blog;

之后，我们所有的表都将创建在这个 blog schema 下，比如 blog.users, blog.posts。这能有效避免和 public schema 下可能存在的其他对象（比如扩展创建的函数）产生命名冲突。

27.2 编写CREATE TABLE语句

现在，我们开始编写核心的建表脚本。我们将把所有语句放在一个文件里（比如 setup_database.sql），并按照正确的依赖顺序来创建表。

依赖顺序：被外键引用的表（如 users, tags）必须先于引用它的表（如 posts, post_tags）被创建。

setup_database.sql

-- 设置搜索路径，这样我们就不需要在每个表名前都写 "blog."
SET search_path TO blog, public;

-- =================================================================
--  1. 用户表 (users)
--  没有外部依赖，最先创建
-- =================================================================
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 为常用查询字段添加索引
CREATE INDEX idx_users_email ON users(email);


-- =================================================================
--  2. 标签表 (tags)
--  没有外部依赖
-- =================================================================
CREATE TABLE tags (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL UNIQUE
);


-- =================================================================
--  3. 文章表 (posts)
--  依赖于 users 表
-- =================================================================
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    author_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE -- 如果用户被删除，他的所有文章也一并删除
);

-- 为常用查询字段添加索引
CREATE INDEX idx_posts_author_id ON posts(author_id);


-- =================================================================
--  4. 评论表 (comments)
--  依赖于 users 表和 posts 表
-- =================================================================
CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    author_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES posts(post_id)
        ON DELETE CASCADE, -- 如果文章被删除，它的所有评论也一并删除

    CONSTRAINT fk_comments_author
        FOREIGN KEY (author_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE -- 如果用户被删除，他的所有评论也一并删除
);

-- 为常用查询字段添加索引
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_author_id ON comments(author_id);


-- =================================================================
--  5. 文章-标签关联表 (post_tags)
--  多对多关系的中间表，依赖于 posts 和 tags
-- =================================================================
CREATE TABLE post_tags (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,

    CONSTRAINT fk_post_tags_post
        FOREIGN KEY (post_id)
        REFERENCES posts(post_id)
        ON DELETE CASCADE, -- 文章删除时，关联关系也删除

    CONSTRAINT fk_post_tags_tag
        FOREIGN KEY (tag_id)
        REFERENCES tags(tag_id)
        ON DELETE CASCADE, -- 标签删除时，关联关系也删除

    -- 复合主键，保证 (文章ID, 标签ID) 的组合是唯一的
    PRIMARY KEY (post_id, tag_id)
);

代码解读与最佳实践

1. SET search_path TO blog, public;: 这是一个非常有用的小技巧。它告诉 PostgreSQL，当遇到一个没有指定 schema 的对象名时，请先去 blog schema 里找，如果找不到，再去 public schema 里找。这样，我们就可以直接写 CREATE TABLE users 而不是 CREATE TABLE blog.users 了。
2. 命名约束 (CONSTRAINT constraint_name ...): 我们为所有的外键约束都明确地起了名字（如 fk_posts_author）。这是一个非常好的习惯！当数据库因为违反约束而报错时，它会直接告诉你哪个名字的约束出了问题，这对于调试非常有帮助。
3. ON DELETE CASCADE: 我们在所有外键上都使用了级联删除。这意味着：
4. 如果一个用户被删除了，他发表的所有文章、所有评论都会被自动删除。
5. 如果一篇文章被删除了，它的所有评论、以及它和标签的所有关联关系都会被自动删除。
   这符合我们这个博客系统的业务逻辑。
6. 复合主键: 在 post_tags 表中，我们使用 PRIMARY KEY (post_id, tag_id) 创建了一个复合主键。这不仅保证了每一行都是唯一的，还自动为这两个列的组合创建了一个高效的 B-Tree 索引，对于查询非常有利。
7. 创建索引: 除了主键和唯一约束自动创建的索引外，我们还为那些将频繁用于 JOIN 或 WHERE 子句的“外键”列（如 posts.author_id, comments.post_id）手动创建了索引。这对于提升查询性能至关重要。

如何执行脚本？

将上面的所有代码保存到一个名为 setup_database.sql 的文件里。然后，在你的命令行中，使用 psql 来执行它：

psql -U your_username -d blog_system_db -f setup_database.sql

• -f setup_database.sql: 告诉 psql 从指定的文件中读取并执行命令。

执行完毕后，你可以用 \c blog_system_db 连接进去，然后用 \dt (显示 blog schema 下的表) 和 \d table_name 来检查我们创建的表、索引和约束是否都已正确就位。

本章小结

成就感满满！我们已经成功地将设计蓝图转化为了一个结构完整、规则严谨、关系清晰的数据库。

• 我们学会了如何组织一个结构化的建表脚本。
• 实践了 CREATE TABLE 的各种高级用法，包括主键、外键、唯一、非空、默认值等约束。
• 理解了为约束命名和为外键创建索引等最佳实践。

我们的博客系统现在已经有了坚实的“骨架”。虽然里面还没有任何数据，但这个骨架已经准备好承载我们未来的所有内容了。

在最后一章，我们将为这个骨架注入灵魂。我们将模拟博客系统的日常操作，编写核心功能的 SQL 查询，比如用户注册、发表文章、查询文章列表等等。准备好让你的数据库“活”起来了吗？我们最终章见！