数据库两问2 questions on databases
关系型 vs 非关系型、主键与外键。Relational vs non-relational databases, primary keys and foreign keys.
这一页有什么On this page3
- 在关系型和文档型之间给出选型理由，而不是背优缺点Give a reason for choosing relational or document storage, instead of reciting pros and cons
- 说清主键和外键各自保证什么Say exactly what a primary key guarantees and what a foreign key guarantees
- 解释外键约束在删除时的几种行为Explain the ways a foreign key constraint can behave on a delete
这两道是全栈岗的入门筛选题。选型那道答「看数据形状和访问模式」比列表格好；主键外键那道会追问到索引和级联删除。These two are screening questions for any full-stack role. For the choice of database, answering "it depends on the shape of the data and how it is read" beats reciting a comparison table. The keys question leads on to indexes and cascading deletes.
关系型数据库 vs 非关系型数据库Relational databases vs non-relational databases
#317 Relational database vs Non-relational database
一句话：关系型先定好表结构、用 JOIN 关联、 强调一致性； 非关系型结构灵活、 按查询模式组织数据、 强调扩展性。
| 关系型（MySQL、PostgreSQL） | 非关系型（MongoDB、Redis） | |
|---|---|---|
| 结构 | 表 + 行 + 列，schema 固定 | 文档 / 键值 / 图，schema 灵活 |
| 关联 | JOIN | 嵌套文档，或应用层自己拼 |
| 事务 | ACID 是强项 | 有但较弱（MongoDB 4.0+ 支持多文档事务） |
| 扩展 | 纵向为主（加配置），分库分表麻烦 | 横向为主（加机器） |
| 适合 | 数据关系复杂、要强一致—— 订单、账务、库存 | 结构多变、读多写多、 单次查询取一整块—— 日志、内容、会话、缓存 |
选型的正确说法：「看数据形状和访问模式」——
- 一次查询要取的东西总是在一起（一篇文章连着它的所有段落）→ 文档型合适。
- 同一份数据要从很多角度关联查（用户 × 订单 × 商品 × 优惠券）→ 关系型合适，因为文档型要么冗余存多份、要么在应用层做 JOIN。
- 需要转账那样的强一致 → 关系型。
会追问：「MongoDB 没有 schema 是优点吗？」——是双刃剑。 前期迭代快，但约束跑到了应用层， 时间长了同一个集合里会存在好几代不同形状的文档。 所以实践中一般还是用 Mongoose 这类工具在应用层加回 schema。
「现在还有清楚的界限吗？」—— 在模糊：PostgreSQL 的jsonb 让它能存文档并建索引， 所以「先上 Postgres， 需要文档就用 jsonb」是很常见的现实选择。
In one line: relational means you define the schema up front, relate rows with JOINs, and lean on consistency; non-relational means a flexible shape, data laid out for the queries you actually run, and easier scale-out.
| Relational (MySQL, PostgreSQL) | Non-relational (MongoDB, Redis) | |
|---|---|---|
| Shape | Tables, rows, columns — fixed schema | Documents / key-value / graph — flexible schema |
| Relating data | JOIN | Nested documents, or you stitch it in the app |
| Transactions | ACID is the whole point | Present but weaker (MongoDB 4.0+ has multi-document) |
| Scaling | Mostly vertical; sharding is painful | Mostly horizontal — add machines |
| Good for | Complex relationships and strong consistency — orders, ledgers, inventory | Shifting shapes, heavy read and write, one query pulling a whole blob — logs, content, sessions, caches |
The right way to talk about choosing: “Look at the shape of the data and the access pattern.”
- Everything one query needs always travels together (an article and all its paragraphs) → document store fits.
- The same data gets related from many angles (users × orders × products × coupons) → relational fits, because a document store either duplicates it or makes you JOIN in application code.
- You need transfer-money-level consistency → relational.
Follow-up: “Is MongoDB being schema-less an advantage?” — it cuts both ways. You move faster early on, but the constraints move into your application code, and given enough time one collection holds three generations of document shapes. Which is why teams reach for something like Mongoose to put a schema back on top.
“Is the line still clear today?” — it is blurring. PostgreSQL’s jsonb stores documents and indexes them, so “start on Postgres, use jsonb where you need a document” is a very common real-world answer.
主键 vs 外键Primary key vs foreign key
#318 Primary key vs Foreign key
一句话：主键唯一标识本表的一行；外键指向另一张表的主键， 用来表达关联并保证引用有效。
| 主键（Primary Key） | 外键（Foreign Key） | |
|---|---|---|
| 作用 | 唯一标识一行 | 指向另一表的主键 |
| 唯一性 | 必须唯一 | 可以重复（一个用户多个订单） |
| 能否为 NULL | 不能 | 可以（表示「暂时没关联」） |
| 每表几个 | 一个（可以是多列组成的复合主键） | 多个 |
| 索引 | 自动建 | 不一定自动建—— MySQL 会，PostgreSQL 不会 |
「外键索引」那一条是加分点： PostgreSQL 里外键列不会自动建索引， 而 JOIN 和级联删除都要用到它 ——忘了手动建索引是很常见的性能问题。
外键的核心价值是引用完整性： 数据库拒绝你插入一条 指向不存在用户的订单， 也拒绝你删掉还有订单的用户。这是数据库帮你兜住的一致性， 不用在应用层写检查。
会追问删除行为—— 这个一定要会：
RESTRICT/NO ACTION——有引用就不许删（默认，最安全）CASCADE——连着子记录一起删（很方便也很危险， 删一个用户可能连带删掉几万条记录）SET NULL—— 把子记录的外键置空 （适合「作者被删了，文章保留为匿名」）
还会追问：「主键用自增 id 还是 UUID？」—— 自增：短、索引局部性好、 但暴露数据量、分库时会冲突。 UUID：全局唯一、 适合分布式和前端预生成， 但更长、随机写入对 B+ 树索引不友好。折中是 ULID / UUIDv7（带时间前缀，有序）—— 这个答出来会显得很专业。
In one line: a primary key uniquely identifies a row in its own table; a foreign key points at another table’s primary key, expressing the relationship and keeping the reference valid.
| Primary key | Foreign key | |
|---|---|---|
| Job | Identifies one row | Points at another table’s primary key |
| Unique? | Must be | Can repeat (one user, many orders) |
| Nullable? | No | Yes — meaning “not linked yet” |
| How many per table | One (possibly composite, several columns) | Many |
| Index | Created for you | Not always — MySQL does, PostgreSQL does not |
That last row is the bonus point. In PostgreSQL a foreign key column gets no index automatically, and both JOINs and cascading deletes need one — forgetting to add it by hand is a very common performance bug.
The real value of a foreign key is referential integrity: the database refuses to insert an order pointing at a user who does not exist, and refuses to delete a user who still has orders. That is consistency the database holds for you, so you do not write the check in application code.
They will ask about delete behaviour — know these three:
RESTRICT/NO ACTION— refuse the delete while references exist (the default, and the safest)CASCADE— delete the children along with it (convenient and dangerous; deleting one user can take tens of thousands of rows with it)SET NULL— null out the child’s foreign key (fits “the author is gone, keep the article as anonymous”)
Another follow-up: “Auto-increment id or UUID?” — auto-increment is short, gives good index locality, but leaks how much data you have and collides when you shard. UUID is globally unique, good for distributed systems and for generating ids on the client, but longer, and random inserts are unkind to a B+ tree index. The middle ground is ULID or UUIDv7 — time-prefixed, so they sort — and saying that makes you sound like you have done this before.
换一道题也能用Works on other problems too
考试不会原题重考。真正能带走的是「看到这种信号 → 伸手去拿这个解法」。The exam will not reuse the same question. What you take away is the reflex: see this signal, reach for that solution.
- 关系型强在关联和事务，文档型强在灵活和横向扩展；选型看数据形状和访问模式。Relational storage is strong on relationships and transactions, document storage on flexibility and scaling out; choose by the shape of the data and how it is read.
- Postgres 的 jsonb 让界限变模糊，「先上 Postgres 需要时用 jsonb」是常见现实选择。jsonb in Postgres blurs the line, and "start with Postgres and use jsonb when you need it" is a common real-world choice.
- 主键唯一非空自动建索引；外键可重复可为空，且 PostgreSQL 不会自动给它建索引。A primary key is unique, cannot be null, and is indexed for you; a foreign key can repeat and can be null, and PostgreSQL does not index it for you.
- 外键的价值是引用完整性；删除行为 RESTRICT / CASCADE / SET NULL 要按业务选。What a foreign key gives you is referential integrity; choose RESTRICT, CASCADE or SET NULL on delete based on what the product needs.