主键 vs 外键
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.