PostgreSQL / 系统课时
理解表之间的关系
理解表之间的关系
本课解决“多张表为什么能正确关联”的问题。你将用主键、外键和唯一约束描述一对一、一对多与多对多关系,并能判断关联字段应该放在哪张表中。
学习目标
- 区分一对一、一对多和多对多关系
- 理解主键、外键与唯一约束的职责
- 根据业务关系选择正确的字段设计
关系由约束表达
表之间的关系不是靠表名自动建立,而是由字段值和约束共同表达。主键负责唯一标识一行,外键要求关联值必须来自另一张表,唯一约束则限制同一个值不能重复出现。
设计时先判断“一个 A 最多对应多少个 B”,再决定外键位置。通常把外键放在“多”的一方;如果双方都只能对应一个,还需要对外键增加唯一约束。
三种常见关系
一对一适合拆分不常使用或权限敏感的数据。例如一个用户只有一份用户档案,可以在档案表中保存 user_id,并为它同时设置外键和唯一约束。
一对多最常见。例如一个用户可以有多张订单,而每张订单只属于一个用户,因此在订单表中保存 user_id。
多对多不能只靠单个外键表达。例如学生可以选择多门课程,一门课程也可以被多个学生选择,需要增加中间表,并把两端主键组成联合主键。
完整建表示例
下面的 SQL 可在新的 PostgreSQL 会话中直接执行,分别创建三种关系。
sql
CREATE TEMP TABLE app_user (
id BIGINT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
CREATE TEMP TABLE user_profile (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE
REFERENCES app_user(id),
display_name VARCHAR(100) NOT NULL
);
CREATE TEMP TABLE customer_order (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL
REFERENCES app_user(id),
amount NUMERIC(10, 2) NOT NULL
);
CREATE TEMP TABLE course (
id BIGINT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL
);
CREATE TEMP TABLE enrollment (
user_id BIGINT REFERENCES app_user(id),
course_id BIGINT REFERENCES course(id),
PRIMARY KEY (user_id, course_id)
);预期输出
text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLEuser_profile.user_id 的 UNIQUE 保证一个用户最多只有一份档案;customer_order.user_id 可以重复,因此一个用户能对应多张订单;enrollment 的联合主键防止同一用户重复选择同一课程。
设计时的判断顺序
先确认两端记录的最大数量,再选择外键位置,最后补充唯一性、非空和删除策略。仅创建同名字段不会产生关系;没有外键时,数据库也无法阻止不存在的关联值。
按自己的节奏,确认真正理解。