按标准做 schema 设计并校验约束

目标

依据数据标准（单词→域→术语）设计模式，确认约束确实生效，按照查询模式创建合适的索引，并能够从元数据中自动生成表定义文档。

为什么重要

如果同一个客户名称在系统中分散为 CUST_NAME(50), CUSTOMER_NM(100), CUST_NM(30)，进行集成查询时就会困难重重，数据会在长度较短的列中被悄悄截断，三年后迁移时还得由人工制作映射表。此外，如果表定义文档写着“必填”，但 DDL 中没有 NOT NULL，这项规定就无法得到执行。只有在数据库中设置约束，才能强制执行。 最后，手工维护的定义文档到开发结束时必然会与实际结构不一致。养成从元数据生成文档的习惯，就能消除这个问题。

步骤

需求：/opt/lab/fixtures/dbo/req/schema-req.md 标准单词：/opt/lab/fixtures/dbo/std/standard-words.csv
创建 /root/db，并创建 sqlite 数据库 /root/db/si.db。（数据库中至少要有一张表才会生成文件，也可以在第 3 步一并完成。）
创建 /root/db/naming.csv。第一行为 logical,physical,domain。将需求中出现的逻辑名称用标准单词组合起来，确定其物理名称。必须至少有 10 行；物理名称只能使用大写字母和下划线，且 domain 不得为空。必须包含 고객명, 주문번호, 주문일자, 주문금액, 상품코드 这五个逻辑名称。
创建以下四张表。 CUSTOMER, PRODUCT, ORDERS, ORDER_ITEM 要求：
- 每张表都要有 PRIMARY KEY
- 通过 FOREIGN KEY 将 ORDERS.CUST_ID → CUSTOMER、ORDER_ITEM.ORD_NO → ORDERS，并将 ORDER_ITEM.PROD_CD → PRODUCT
- 为 ORDER_ITEM.QTY 设置只允许正数的 CHECK
- 为 ORDERS.ORD_STS_CD 设置只允许 '01','02','03','09' 的 CHECK
- 每张表都要有 REG_DT 列，并设置 NOT NULL 和默认值
- 为 CUSTOMER.CUST_EMAIL 设置 UNIQUE

创建 /root/db/constraint.txt。文件共三行，每一行都保存一次违规尝试所得结果消息的第一行。

qty=<QTY 를 0 으로 INSERT 했을 때 오류 메시지>
status=<ORD_STS_CD 를 '99' 로 INSERT 했을 때 오류 메시지>
email=<CUST_EMAIL 을 중복으로 INSERT 했을 때 오류 메시지>

（请在副本中测试，不要污染原始数据库。）

创建三个索引。
- IX_ORDERS_01：用于 WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?
- IX_ORDERS_02：用于 WHERE ORD_DT = ? AND ORD_STS_CD = ?
- IX_ORDER_ITEM_01：用于 WHERE PROD_CD = ? 列的顺序也必须正确。
将 /opt/lab/fixtures/dbo/seed/ 中的四个 CSV 分别加载到对应表中。 CUSTOMER 应有 200 行，PRODUCT 应有 50 行，ORDERS 应有 1000 行，ORDER_ITEM 应有 2400 行。
创建视图 V_DAILY_SALES。其列为 ORD_DT, ORD_CNT, AMT_SUM，只汇总**排除取消状态（09）**后的订单，并按 ORD_DT 升序排列。
创建 /root/db/table-def.csv。第一行为 table,column,type,notnull,pk。 从数据库元数据中提取四张表的所有列。保持原有的表名和列顺序。

参考

启用 sqlite 外键：PRAGMA foreign_keys = ON;（每个连接都必须设置）
元数据：PRAGMA table_info(<테이블>);, PRAGMA foreign_key_list(<테이블>);
加载 CSV：.mode csv / .import --skip 1 <파일> <테이블>
常见错误 1：没有启用 PRAGMA foreign_keys，导致不检查 FK。 sqlite 默认关闭外键检查。
常见错误 2：只在文档中写了 CHECK，却没有将其加入 DDL。
常见错误 3：把复合索引中的列顺序颠倒。

创建数据库

创建 /root/db，并创建 sqlite 数据库 /root/db/si.db。（数据库中至少要有一张表才会生成文件，也可以在第 3 步一并完成。）

sqlite 数据库就是一个文件。请记住，外键检查默认处于关闭状态。

标准术语映射表

创建 /root/db/naming.csv。第一行为 logical,physical,domain。将需求中出现的逻辑名称用标准单词组合起来，确定其物理名称。必须至少有 10 行；物理名称只能使用大写字母和下划线，且 domain 不得为空。必须包含 고객명, 주문번호, 주문일자, 주문금액, 상품코드 这五个逻辑名称。

使用标准单词词典组合出物理名称。同一个含义的单词如果出现两次，其缩写也必须一致。

创建表

创建以下四张表。 CUSTOMER, PRODUCT, ORDERS, ORDER_ITEM 要求：

每张表都要有 PRIMARY KEY
通过 FOREIGN KEY 将 ORDERS.CUST_ID → CUSTOMER、ORDER_ITEM.ORD_NO → ORDERS，并将 ORDER_ITEM.PROD_CD → PRODUCT
为 ORDER_ITEM.QTY 设置只允许正数的 CHECK
为 ORDERS.ORD_STS_CD 设置只允许 '01','02','03','09' 的 CHECK
每张表都要有 REG_DT 列，并设置 NOT NULL 和默认值
为 CUSTOMER.CUST_EMAIL 设置 UNIQUE

约束必须写在 DDL 中，而不是只写在文档中，才能强制执行。请思考应分别用哪种约束来表示必填项、代码值范围，以及防止业务键重复。

确认约束生效

创建 /root/db/constraint.txt。文件共三行，每一行都保存一次违规尝试所得结果消息的第一行。

qty=<QTY 를 0 으로 INSERT 했을 때 오류 메시지>
status=<ORD_STS_CD 를 '99' 로 INSERT 했을 때 오류 메시지>
email=<CUST_EMAIL 을 중복으로 INSERT 했을 때 오류 메시지>

（请在副本中测试，不要污染原始数据库。）

要确认约束是否真的能够拦截违规操作，就必须尝试插入违反约束的数据。请养成在副本中测试、避免污染原始数据库的习惯。

基于查询模式的索引

创建三个索引。

IX_ORDERS_01：用于 WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?
IX_ORDERS_02：用于 WHERE ORD_DT = ? AND ORD_STS_CD = ?
IX_ORDER_ITEM_01：用于 WHERE PROD_CD = ? 列的顺序也必须正确。

对于复合索引，列顺序至关重要。基本规则是把等值条件列放在前面，把范围条件列放在后面。

加载示例数据

将 /opt/lab/fixtures/dbo/seed/ 中的四个 CSV 分别加载到对应表中。 CUSTOMER 应有 200 行，PRODUCT 应有 50 行，ORDERS 应有 1000 行，ORDER_ITEM 应有 2400 行。

加载 CSV 时，请留意表头行的处理方式和分隔符设置。加载完成后务必核对行数。

创建汇总视图

创建视图 V_DAILY_SALES。其列为 ORD_DT, ORD_CNT, AMT_SUM，只汇总**排除取消状态（09）**后的订单，并按 ORD_DT 升序排列。

视图也是强制统一查询标准的一种手段。将逻辑删除条件写进视图，可以减少查询时遗漏条件的事故。

自动生成表定义文档

创建 /root/db/table-def.csv。第一行为 table,column,type,notnull,pk。 从数据库元数据中提取四张表的所有列。保持原有的表名和列顺序。

手工维护的文档很快就会与事实不符。从数据库元数据中生成文档，才能确保它始终与实际结构一致。