MariaDB 教程 / 设计一对多、多对多关系与外键
设计表关系时,应先明确每行代表的事实,再用主键与外键连接这些事实。一位客户可以有多个订单;订单与商品之间的多对多关系,可以通过订单明细表表示,并在明细中保存数量和购买时的单价。
定义每行的含义与关系方向
如果把客户姓名与商品清单塞进一条订单文本,数量计算就依赖字符串拆分,客户资料更新也容易留下重复且不一致的文本。把客户、商品、订单和明细分开,是为了保存具有不同更新规则的事实,不是单纯为了增加表的数量。
| 表 | 一行代表什么 | 标识或连接方式 |
|---|---|---|
| customers | 一位客户 | customer_id 主键 |
| products | 一种商品 | product_id 主键 |
| orders | 一个订单 | order_id 主键,customer_id 指向客户 |
| items | 订单中的一种商品 | order_id 与 product_id 组成复合主键 |
明细保存购买时的 unit_price,而不是每次都从商品当前价格反推旧订单。商品日后调价,旧明细中的数量和成交单价仍可用于重现原金额。若同一订单允许同一商品以多个规格、优惠或独立行出现,应重新评估是否需要明细行编号或规格键;本节的复合主键是简化模型。
图中“0 到多个”表示这里的结构允许客户暂时没有订单、订单暂时没有明细、商品暂时没有销售记录。每条已存在的明细仍必须引用一个有效订单和一种有效商品。外键的详细规则见 MariaDB 官方 外键文档。
按依赖顺序创建完整的四表样本
以 course_user 连接练习服务器,先核对 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。本节使用 c25_customers、c25_products、c25_orders、c25_items 四张独立表。若同名表已存在,先检查定义与数据,不要覆盖用途不明的对象。
下面是完整的建表与插入代码,应在 MariaDB 客户端中执行。可按照 UTF-8 SQL 文件与 SOURCE 教程保存后用实际路径加载,例如 SOURCE C:/mariadb-lab/lesson.sql;。先建客户与商品,再建引用它们的订单和明细;插入数据也遵循父记录先于子记录的顺序。
所有表使用 InnoDB,相关编号都使用一致的 INT 类型。主键和复合键的基本含义可参照 PRIMARY KEY 与复合键教程。外键不只是名称相似的两个字段,而是由数据库实际执行的引用约束。
USE shopcourse;
CREATE TABLE c25_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c25_order_customer FOREIGN KEY (customer_id)
REFERENCES c25_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c25_item_order FOREIGN KEY (order_id)
REFERENCES c25_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c25_item_product FOREIGN KEY (product_id)
REFERENCES c25_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c25_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c25_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c25_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c25_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
客户姓名、城市和商品名保留原始韩文 UTF-8 字符串,以稳定编号解释结果。商品 1~4 分别为笔记本、圆珠笔、马克杯和台灯;客户 1、3 的城市值表示首尔,2 表示釜山,4 表示大田。复现实验时请原样保存字面量,不要自行翻译后再比较结果。
核对一对多与多对多的实际连接
SHOW CREATE TABLE c25_items;
SELECT c.customer_id,c.customer_name,o.order_id
FROM c25_customers AS c JOIN c25_orders AS o
ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;
SELECT COUNT(*) AS customers FROM c25_customers;
SELECT COUNT(*) AS orders_count FROM c25_orders;
SELECT COUNT(*) AS detail_rows FROM c25_items;
预期有四位客户、四个订单、六条明细。客户 1 对应订单 101、102,是客户到订单的一对多。订单 101 同时包含商品 1、2;商品 1 又出现在订单 101、103 中,订单与商品的多对多关系由这些明细行连接。
每条明细还保存 quantity 和 unit_price,所以它不只是两个编号的清单,而是包含本次购买事实的记录。例如订单 101 的商品 1 数量为 2、单价为 3500,行金额是 7000。商品目录中的现价变化,不应自动改写这项历史事实。
关系应使用稳定编号,不应直接依靠显示姓名。不同客户可能同名,姓名也可能更新;把标识与业务唯一性条件分开,可以避免显示文本改变时重建所有订单连接。本例客户 4 没有订单,商品 4 没有销售明细,但它们仍是有效的父表记录。
分别测试不存在的父记录与受限制的删除
先完成合法样本,再把下面两条故意失败的语句分别执行。第一条订单指向不存在的客户 999;第二条试图删除仍被订单引用的客户 1。它们用于确认约束在起作用,不应与正常的完整初始化文件一起连续运行并忽略错误。
INSERT INTO c25_orders(order_id,customer_id,ordered_at,status)
VALUES(999,999,'2026-03-01 10:00:00','paid');
DELETE FROM c25_customers WHERE customer_id=1;
第一条应因无效父引用被拒绝,通常可看到错误 1452;第二条因 RESTRICT 阻止删除,通常为错误 1451。每次失败后,再查询客户 1 是否仍存在,以及订单总数是否仍为 4。预期新订单 999 没有被插入,原客户和订单都没有因此消失。
不要为了让输入通过就关闭 foreign_key_checks。应修正引用编号,或先建立真实且有效的父记录。若其他模型允许子表引用列为 NULL,可能用来表示没有父对象;但本例订单的 customer_id 明确为 NOT NULL,不接受“没有客户”的订单。
复合主键还会拒绝在同一订单中重复插入同一种商品;数量必须大于零,则由 CHECK 约束检查。不同约束保护不同条件,不能把所有失败都解释成外键错误。检查错误信息和 SHOW CREATE TABLE,比只看到脚本中有 REFERENCES 文本更有意义。
根据数据保留规则选择删除策略
CASCADE 可以把父记录删除传播到子记录,但操作方便不是选择它的充分理由。客户离开后,哪些数据保留、匿名化或删除,取决于具体业务和适用规则;订单明细还可能承载退货、结算或审计所需的历史。本例使用 RESTRICT,避免一次删除自动带走相关订单。
如果约束创建失败,先检查父子字段的数据类型与整数符号属性是否兼容、索引是否适合、双方存储引擎是否支持。不要只给两个字段起相同名称,就认为它们已经建立关系。创建后读取真实定义,再用已知无效输入核对拒绝行为。
引用完整性不能覆盖全部业务规则。它能检查明细引用的订单是否存在,但不会单靠外键要求每个订单至少有一条明细;也不会自动扣减库存或检查所有可售条件。需要这些规则时,应另行设计事务流程与验证。
审查可选关系,并按子表在前的顺序清理
样本较小时,逐个提问并检查记录:客户能否先存在再下单?商品能否先建档再销售?订单是否必须有客户?每个明细是否都能定位订单与商品?除了关系的最大数量,还应明确哪些状态允许暂时为空,哪些必须在提交业务操作前满足。
还应区分商品的当前属性与订单发生时的事实。要验证历史价格,可在独立练习事务中仅修改商品现价,再查询原明细单价与行金额,最后回滚目录改价;不要为模拟历史而修改成交明细。本节所有试验都应局限于可丢弃的学习数据。
重练前先检查四表定义与记录,备份应保留的内容。下面按依赖关系先删明细,再删订单,最后删除商品和客户,只适用于已确认可丢弃的本节四张表。DROP TABLE 会删除定义和数据,普通 ROLLBACK 无法恢复;不要关闭外键检查后无序删除其他表。
DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;





