KingbaseES 关系建模与完整性约束设计以 kb_shop 为例本文是本系列第 5 篇。前面已经创建了kb_shop数据库和sales、inventory、ops模式本文开始创建客户、商品、订单和订单明细表。引言上一篇文章其实我们已经把kb_shop这个库里面的sales、inventory还有ops这三个模式给划分出来了。也就是说业务对象怎么分层这个事儿算是搞定了。那么接着呢我们就要开始搞真正的数据建模了。这个阶段通常来说要面对的问题就是到底哪些业务实体的情况需要我们去建表每一张表里面的话又该放哪些字段主键这块怎么设计还有那个表跟表之间外键关系又是怎么建立起来的这篇文章咱们就围绕一个比较简单的但是完整度够了的订单业务模型来弄。我们会去建客户表、商品表还有那个订单主表以及订单明细表。写的时候呢我会把建表 SQL 和关系模型这两个东西放在一起来讲。其实往往仅仅只是告诉你们“去执行这段 SQL”是不够的。那为什么要这样去拆表呢为什么要去设置主键还有唯一约束、检查约束以及外键约束这些个东西呢这些原因我都会给大伙说清楚。通常来说你去学数据库真正开始接触业务场景了往往都是从建表这一步开始的。不过建表这个事儿它并不是说你简单把字段往那一列就完事了。你想弄出一个比较靠谱的表结构的话通常来说你至少得回答出下面这些个问题每一行的数据你到底怎么去唯一标识它哪些字段是必须要填写的也就是不能为空的情况又有哪些字段是不允许出现重复值的的哪些字段里面不能塞那些非法的值进去表跟表之间它们究竟是一个什么样的关系本文会围绕kb_shop创建 4 张核心表模式表名用途salescustomer客户信息inventoryproduct商品信息和库存salescustomer_order订单主表salesorder_item订单明细文章目录KingbaseES 关系建模与完整性约束设计以 kb_shop 为例引言关系模型、主键与完整性约束一、连接 kb_shop 并确认模式二、业务表关系设计三、创建客户表 sales.customer四、创建商品表 inventory.product五、创建订单主表 sales.customer_order六、创建订单明细表 sales.order_item七、查看所有业务表八、给表和字段添加备注九、测试约束是否生效1. 测试手机号唯一约束2. 测试价格检查约束3. 测试外键约束十、常见问题排查问题 1创建订单表时报外键引用表不存在问题 2表已经存在问题 3插入中文后显示乱码十一、本文小结关系模型、主键与完整性约束其实吧关系型数据库最核心的东西根本就不是说“表格长得跟Excel一样”。那是表面现象。它真正的关键点在哪呢也就是说得靠关系模型去把业务里的实体还有它们之间的约束给表达出来。你看啊客户、商品还有订单、订单明细这几个其实就是不同的实体。那它们之间是有明确关系的。客户他会有订单订单里面呢又包含了明细而这个明细它又引用了商品。那么我们在建表的时候最要紧的事其实并不是说字段加的越多就越好这个误区很多人都有。真正该做的事是要把数据完整性的那些个规则给落到数据库里面去。通常来说呢常见的完整性也就分那么三类类型解决的问题本文示例实体完整性每一行数据如何唯一识别PRIMARY KEY域完整性某个字段允许什么值NOT NULL、CHECK、字段类型参照完整性表与表之间引用是否真实存在FOREIGN KEY如果说这些个规则你仅仅只是在应用代码里面去写那问题就来了。你换个导入脚本的情况或者说换个人来维护往往仅仅只是操作不当就可能把规则给绕过去了。数据就出岔子了这是一个问题。那怎么办呢其实你只要把主键啊唯一约束还有检查约束以及外键约束统统给定义在 KingbaseES 里面去。这样的话数据库它就能变成数据质量兜底的那个东西了。那么这篇文章里面我们建的这 4 张表你可别觉得我是为了凑个数弄个示例出来。其实它们是构成了一个订单模型虽然说是最小的但是它是完整的。接着后面我们要去讲查询啊还有事务以及索引和备份这些个东西的时候往往都会基于这一组关系来给大家展开说。一、连接 kb_shop 并确认模式进入工具目录cd /d D:\Tools\Kingbase\ES\Server\bin连接kb_shopksql -U system -d kb_shop -h localhost -p 54321确认当前数据库SELECTcurrent_database()AScurrent_db,current_userASlogin_user;查看模式\dn如果还没有sales和inventory先创建CREATESCHEMAIFNOTEXISTSsales;CREATESCHEMAIFNOTEXISTSinventory;CREATESCHEMAIFNOTEXISTSops;二、业务表关系设计那么我们先来看看逻辑关系这块。其实在你动手去写CREATE TABLE之前先把关系给理清楚这个在数据库建模里面是很关键的一步。也就是说表结构它不是自己孤立存在在那里的。客户啊订单啊还有商品它们相互之间的关系会直接影响到后面你的外键怎么建索引怎么加还有查询语句到底怎么写。那如果一开始关系就没想明白的话后面你往往会碰到大麻烦。什么麻烦呢很容易就会出现字段重复录入的情况或者说产生一堆冗余数据再或者就是统计口径乱七八糟的。如果用一句话来概括的话就是客户下订单订单包含商品明细商品表维护价格和库存。这个模型的话其实足够简单了。但是呢它能够支撑你后面去搞很多主题的情况。你比如什么增删改查啦还有事务扣库存啦索引优化视图报表备份恢复包括权限控制这些都能拿它来练手。三、创建客户表 sales.customer客户表这个表呢其实就是用来保存客户的一些最基础的信息的。那么设计客户表重点在哪里呢其实就是两点一个是怎么“唯一识别客户”另一个就是怎么“控制客户状态”。在这里的话我们是用customer_id来当做内部的主键。接着呢用phone这个字段来当做业务上的唯一值。为什么要这么搞呢这样搞的好处就是既方便了数据库内部去做关联也能避免同一个手机号被人家重复注册的情况出现。CREATETABLEIFNOTEXISTSsales.customer(customer_idSERIALPRIMARYKEY,customer_nameVARCHAR(80)NOTNULL,phoneVARCHAR(30)NOTNULL,emailVARCHAR(120),customer_levelVARCHAR(20)NOTNULLDEFAULTnormal,statusVARCHAR(20)NOTNULLDEFAULTactive,created_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,CONSTRAINTuk_customer_phoneUNIQUE(phone),CONSTRAINTck_customer_levelCHECK(customer_levelIN(normal,vip)),CONSTRAINTck_customer_statusCHECK(statusIN(active,disabled)));字段说明字段类型说明customer_idSERIAL自增主键customer_nameVARCHAR(80)客户姓名phoneVARCHAR(30)手机号不能重复emailVARCHAR(120)邮箱可以留空customer_levelVARCHAR(20)客户等级statusVARCHAR(20)客户状态created_atTIMESTAMP创建的时间那么在这个表里面呢我们一共用了 3 类约束约束作用PRIMARY KEY用来标识每一行客户是唯一的UNIQUE就是限制手机号不能重复CHECK客户等级还有状态只能填我们规定的那些值接着我们来看看表结构\d sales.customer四、创建商品表 inventory.product商品表保存商品名称、价格、库存和上下架状态。商品表承担两个角色一是商品资料二是库存状态。为了让后续事务扣库存更直观本文把stock_qty放在商品表中演示。真实系统里库存也可能拆成独立库存表但学习阶段这样更容易理解。CREATETABLEIFNOTEXISTSinventory.product(product_idSERIALPRIMARYKEY,product_codeVARCHAR(40)NOTNULL,product_nameVARCHAR(120)NOTNULL,categoryVARCHAR(50)NOTNULL,unit_priceNUMERIC(12,2)NOTNULL,stock_qtyINTNOTNULLDEFAULT0,statusVARCHAR(20)NOTNULLDEFAULTon_sale,created_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,CONSTRAINTuk_product_codeUNIQUE(product_code),CONSTRAINTck_product_priceCHECK(unit_price0),CONSTRAINTck_product_stockCHECK(stock_qty0),CONSTRAINTck_product_statusCHECK(statusIN(on_sale,off_sale)));几个字段值得注意字段说明product_code商品编码适合对接外部系统unit_price使用NUMERIC(12,2)适合金额stock_qty当前库存不能小于 0status商品上下架状态金额不建议用浮点类型。这里使用NUMERIC(12,2)可以精确表达小数金额。查看表结构\d inventory.product五、创建订单主表 sales.customer_order订单主表保存订单的整体信息。订单主表只保存订单级别的信息例如订单号、客户、状态、总金额和支付时间。它不直接保存每个商品明细因为一个订单可能包含多个商品。把主表和明细表拆开是典型的一对多关系设计。CREATETABLEIFNOTEXISTSsales.customer_order(order_idSERIALPRIMARYKEY,order_noVARCHAR(40)NOTNULL,customer_idINTNOTNULL,order_statusVARCHAR(20)NOTNULLDEFAULTcreated,total_amountNUMERIC(12,2)NOTNULLDEFAULT0,created_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,paid_atTIMESTAMP,CONSTRAINTuk_customer_order_noUNIQUE(order_no),CONSTRAINTck_customer_order_statusCHECK(order_statusIN(created,paid,cancelled)),CONSTRAINTck_customer_order_amountCHECK(total_amount0),CONSTRAINTfk_order_customerFOREIGNKEY(customer_id)REFERENCESsales.customer(customer_id));这里出现了外键CONSTRAINTfk_order_customerFOREIGNKEY(customer_id)REFERENCESsales.customer(customer_id)它的作用是保证订单必须属于一个真实存在的客户。如果尝试给不存在的customer_id创建订单数据库会拒绝。这就是关系型数据库约束的价值把一部分数据正确性直接交给数据库守住。六、创建订单明细表 sales.order_item订单明细表保存订单里买了哪些商品、买了几件、单价是多少。订单明细表是订单和商品之间的关联载体。它既引用订单也引用商品同时保存购买数量、成交单价和行金额。这样既能还原订单内容也能支持后续按商品统计销量。CREATETABLEIFNOTEXISTSsales.order_item(item_idSERIALPRIMARYKEY,order_idINTNOTNULL,product_idINTNOTNULL,quantityINTNOTNULL,unit_priceNUMERIC(12,2)NOTNULL,line_amountNUMERIC(12,2)NOTNULL,CONSTRAINTck_order_item_quantityCHECK(quantity0),CONSTRAINTck_order_item_unit_priceCHECK(unit_price0),CONSTRAINTck_order_item_line_amountCHECK(line_amount0),CONSTRAINTfk_item_orderFOREIGNKEY(order_id)REFERENCESsales.customer_order(order_id),CONSTRAINTfk_item_productFOREIGNKEY(product_id)REFERENCESinventory.product(product_id));为什么订单明细里要保存unit_price而不是每次都从商品表查因为商品价格会变化。订单明细保存的是下单当时的成交价不能因为商品后来改价而影响历史订单。这是业务建模中非常常见的设计。七、查看所有业务表查看sales模式下的表\dt sales.*查看inventory模式下的表\dt inventory.*也可以通过 SQL 查看SELECTtable_schema,table_nameFROMinformation_schema.tablesWHEREtable_schemaIN(sales,inventory)ORDERBYtable_schema,table_name;预期结果八、给表和字段添加备注技术文章里可以不加备注但真实数据库里建议加。备注可以帮助后来的人理解对象含义。COMMENTONTABLEsales.customerISCustomer basic information;COMMENTONTABLEinventory.productISProduct and stock information;COMMENTONTABLEsales.customer_orderISOrder header table;COMMENTONTABLEsales.order_itemISOrder line item table;给关键字段加备注COMMENTONCOLUMNsales.customer.customer_levelISnormal or vip;COMMENTONCOLUMNinventory.product.stock_qtyISCurrent available stock quantity;COMMENTONCOLUMNsales.customer_order.order_statusIScreated, paid or cancelled;COMMENTONCOLUMNsales.order_item.line_amountISquantity multiplied by unit_price;查看详细结构\dsales.customer九、测试约束是否生效1. 测试手机号唯一约束先插入一条客户INSERTINTOsales.customer(customer_name,phone,email,customer_level)VALUES(张三,13800000001,zhangsanexample.com,normal);再插入相同手机号INSERTINTOsales.customer(customer_name,phone,email,customer_level)VALUES(李四,13800000001,lisiexample.com,vip);第二条会失败因为phone有唯一约束。2. 测试价格检查约束INSERTINTOinventory.product(product_code,product_name,category,unit_price,stock_qty)VALUES(P_BAD,错误价格商品,test,-10,1);这条会失败因为unit_price 0。3. 测试外键约束INSERTINTOsales.customer_order(order_no,customer_id,order_status,total_amount)VALUES(SO_BAD_001,999999,created,100);如果不存在customer_id 999999的客户这条会失败。十、常见问题排查问题 1创建订单表时报外键引用表不存在原因通常是建表顺序错了。正确顺序sales.customer inventory.product sales.customer_order sales.order_item因为订单表依赖客户表订单明细依赖订单表和商品表。问题 2表已经存在本文 SQL 使用了CREATETABLEIFNOTEXISTS...如果表已经存在通常不会重复创建。但如果你修改了表结构IF NOT EXISTS不会自动帮你改旧表。学习阶段如果想重建可以先按依赖顺序删除DROPTABLEIFEXISTSsales.order_item;DROPTABLEIFEXISTSsales.customer_order;DROPTABLEIFEXISTSinventory.product;DROPTABLEIFEXISTSsales.customer;注意删除表会删除数据执行前要确认。问题 3插入中文后显示乱码优先确认数据库编码和客户端编码。查看数据库编码\l kb_shop查看客户端编码SHOWclient_encoding;如果终端显示异常也要检查 Windows 命令行字体和代码页。十一、本文小结本文承接第四篇的 Schema 分层设计在sales和inventory模式下完成了核心业务表建模。到这里kb_shop已经从一个空业务库逐步演变成包含客户、商品、订单和订单明细的关系模型。本文完成了kb_shop的核心业务建模sales.customer 客户表 inventory.product 商品表 sales.customer_order 订单主表 sales.order_item 订单明细表并实践了SERIAL 自增主键 VARCHAR 字符字段 NUMERIC 金额字段 TIMESTAMP 时间字段 PRIMARY KEY 主键 UNIQUE 唯一约束 CHECK 检查约束 FOREIGN KEY 外键约束 COMMENT 备注下一篇会开始插入真实样例数据并用SELECT、过滤、排序、分页、聚合和多表关联查询把这些表真正用起来。第五篇解决的是“表如何设计”第六篇会继续解决“数据如何查询、统计和解释”。
KingbaseES 关系建模与完整性约束设计：以 kb_shop 为例
KingbaseES 关系建模与完整性约束设计以 kb_shop 为例本文是本系列第 5 篇。前面已经创建了kb_shop数据库和sales、inventory、ops模式本文开始创建客户、商品、订单和订单明细表。引言上一篇文章其实我们已经把kb_shop这个库里面的sales、inventory还有ops这三个模式给划分出来了。也就是说业务对象怎么分层这个事儿算是搞定了。那么接着呢我们就要开始搞真正的数据建模了。这个阶段通常来说要面对的问题就是到底哪些业务实体的情况需要我们去建表每一张表里面的话又该放哪些字段主键这块怎么设计还有那个表跟表之间外键关系又是怎么建立起来的这篇文章咱们就围绕一个比较简单的但是完整度够了的订单业务模型来弄。我们会去建客户表、商品表还有那个订单主表以及订单明细表。写的时候呢我会把建表 SQL 和关系模型这两个东西放在一起来讲。其实往往仅仅只是告诉你们“去执行这段 SQL”是不够的。那为什么要这样去拆表呢为什么要去设置主键还有唯一约束、检查约束以及外键约束这些个东西呢这些原因我都会给大伙说清楚。通常来说你去学数据库真正开始接触业务场景了往往都是从建表这一步开始的。不过建表这个事儿它并不是说你简单把字段往那一列就完事了。你想弄出一个比较靠谱的表结构的话通常来说你至少得回答出下面这些个问题每一行的数据你到底怎么去唯一标识它哪些字段是必须要填写的也就是不能为空的情况又有哪些字段是不允许出现重复值的的哪些字段里面不能塞那些非法的值进去表跟表之间它们究竟是一个什么样的关系本文会围绕kb_shop创建 4 张核心表模式表名用途salescustomer客户信息inventoryproduct商品信息和库存salescustomer_order订单主表salesorder_item订单明细文章目录KingbaseES 关系建模与完整性约束设计以 kb_shop 为例引言关系模型、主键与完整性约束一、连接 kb_shop 并确认模式二、业务表关系设计三、创建客户表 sales.customer四、创建商品表 inventory.product五、创建订单主表 sales.customer_order六、创建订单明细表 sales.order_item七、查看所有业务表八、给表和字段添加备注九、测试约束是否生效1. 测试手机号唯一约束2. 测试价格检查约束3. 测试外键约束十、常见问题排查问题 1创建订单表时报外键引用表不存在问题 2表已经存在问题 3插入中文后显示乱码十一、本文小结关系模型、主键与完整性约束其实吧关系型数据库最核心的东西根本就不是说“表格长得跟Excel一样”。那是表面现象。它真正的关键点在哪呢也就是说得靠关系模型去把业务里的实体还有它们之间的约束给表达出来。你看啊客户、商品还有订单、订单明细这几个其实就是不同的实体。那它们之间是有明确关系的。客户他会有订单订单里面呢又包含了明细而这个明细它又引用了商品。那么我们在建表的时候最要紧的事其实并不是说字段加的越多就越好这个误区很多人都有。真正该做的事是要把数据完整性的那些个规则给落到数据库里面去。通常来说呢常见的完整性也就分那么三类类型解决的问题本文示例实体完整性每一行数据如何唯一识别PRIMARY KEY域完整性某个字段允许什么值NOT NULL、CHECK、字段类型参照完整性表与表之间引用是否真实存在FOREIGN KEY如果说这些个规则你仅仅只是在应用代码里面去写那问题就来了。你换个导入脚本的情况或者说换个人来维护往往仅仅只是操作不当就可能把规则给绕过去了。数据就出岔子了这是一个问题。那怎么办呢其实你只要把主键啊唯一约束还有检查约束以及外键约束统统给定义在 KingbaseES 里面去。这样的话数据库它就能变成数据质量兜底的那个东西了。那么这篇文章里面我们建的这 4 张表你可别觉得我是为了凑个数弄个示例出来。其实它们是构成了一个订单模型虽然说是最小的但是它是完整的。接着后面我们要去讲查询啊还有事务以及索引和备份这些个东西的时候往往都会基于这一组关系来给大家展开说。一、连接 kb_shop 并确认模式进入工具目录cd /d D:\Tools\Kingbase\ES\Server\bin连接kb_shopksql -U system -d kb_shop -h localhost -p 54321确认当前数据库SELECTcurrent_database()AScurrent_db,current_userASlogin_user;查看模式\dn如果还没有sales和inventory先创建CREATESCHEMAIFNOTEXISTSsales;CREATESCHEMAIFNOTEXISTSinventory;CREATESCHEMAIFNOTEXISTSops;二、业务表关系设计那么我们先来看看逻辑关系这块。其实在你动手去写CREATE TABLE之前先把关系给理清楚这个在数据库建模里面是很关键的一步。也就是说表结构它不是自己孤立存在在那里的。客户啊订单啊还有商品它们相互之间的关系会直接影响到后面你的外键怎么建索引怎么加还有查询语句到底怎么写。那如果一开始关系就没想明白的话后面你往往会碰到大麻烦。什么麻烦呢很容易就会出现字段重复录入的情况或者说产生一堆冗余数据再或者就是统计口径乱七八糟的。如果用一句话来概括的话就是客户下订单订单包含商品明细商品表维护价格和库存。这个模型的话其实足够简单了。但是呢它能够支撑你后面去搞很多主题的情况。你比如什么增删改查啦还有事务扣库存啦索引优化视图报表备份恢复包括权限控制这些都能拿它来练手。三、创建客户表 sales.customer客户表这个表呢其实就是用来保存客户的一些最基础的信息的。那么设计客户表重点在哪里呢其实就是两点一个是怎么“唯一识别客户”另一个就是怎么“控制客户状态”。在这里的话我们是用customer_id来当做内部的主键。接着呢用phone这个字段来当做业务上的唯一值。为什么要这么搞呢这样搞的好处就是既方便了数据库内部去做关联也能避免同一个手机号被人家重复注册的情况出现。CREATETABLEIFNOTEXISTSsales.customer(customer_idSERIALPRIMARYKEY,customer_nameVARCHAR(80)NOTNULL,phoneVARCHAR(30)NOTNULL,emailVARCHAR(120),customer_levelVARCHAR(20)NOTNULLDEFAULTnormal,statusVARCHAR(20)NOTNULLDEFAULTactive,created_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,CONSTRAINTuk_customer_phoneUNIQUE(phone),CONSTRAINTck_customer_levelCHECK(customer_levelIN(normal,vip)),CONSTRAINTck_customer_statusCHECK(statusIN(active,disabled)));字段说明字段类型说明customer_idSERIAL自增主键customer_nameVARCHAR(80)客户姓名phoneVARCHAR(30)手机号不能重复emailVARCHAR(120)邮箱可以留空customer_levelVARCHAR(20)客户等级statusVARCHAR(20)客户状态created_atTIMESTAMP创建的时间那么在这个表里面呢我们一共用了 3 类约束约束作用PRIMARY KEY用来标识每一行客户是唯一的UNIQUE就是限制手机号不能重复CHECK客户等级还有状态只能填我们规定的那些值接着我们来看看表结构\d sales.customer四、创建商品表 inventory.product商品表保存商品名称、价格、库存和上下架状态。商品表承担两个角色一是商品资料二是库存状态。为了让后续事务扣库存更直观本文把stock_qty放在商品表中演示。真实系统里库存也可能拆成独立库存表但学习阶段这样更容易理解。CREATETABLEIFNOTEXISTSinventory.product(product_idSERIALPRIMARYKEY,product_codeVARCHAR(40)NOTNULL,product_nameVARCHAR(120)NOTNULL,categoryVARCHAR(50)NOTNULL,unit_priceNUMERIC(12,2)NOTNULL,stock_qtyINTNOTNULLDEFAULT0,statusVARCHAR(20)NOTNULLDEFAULTon_sale,created_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,CONSTRAINTuk_product_codeUNIQUE(product_code),CONSTRAINTck_product_priceCHECK(unit_price0),CONSTRAINTck_product_stockCHECK(stock_qty0),CONSTRAINTck_product_statusCHECK(statusIN(on_sale,off_sale)));几个字段值得注意字段说明product_code商品编码适合对接外部系统unit_price使用NUMERIC(12,2)适合金额stock_qty当前库存不能小于 0status商品上下架状态金额不建议用浮点类型。这里使用NUMERIC(12,2)可以精确表达小数金额。查看表结构\d inventory.product五、创建订单主表 sales.customer_order订单主表保存订单的整体信息。订单主表只保存订单级别的信息例如订单号、客户、状态、总金额和支付时间。它不直接保存每个商品明细因为一个订单可能包含多个商品。把主表和明细表拆开是典型的一对多关系设计。CREATETABLEIFNOTEXISTSsales.customer_order(order_idSERIALPRIMARYKEY,order_noVARCHAR(40)NOTNULL,customer_idINTNOTNULL,order_statusVARCHAR(20)NOTNULLDEFAULTcreated,total_amountNUMERIC(12,2)NOTNULLDEFAULT0,created_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,paid_atTIMESTAMP,CONSTRAINTuk_customer_order_noUNIQUE(order_no),CONSTRAINTck_customer_order_statusCHECK(order_statusIN(created,paid,cancelled)),CONSTRAINTck_customer_order_amountCHECK(total_amount0),CONSTRAINTfk_order_customerFOREIGNKEY(customer_id)REFERENCESsales.customer(customer_id));这里出现了外键CONSTRAINTfk_order_customerFOREIGNKEY(customer_id)REFERENCESsales.customer(customer_id)它的作用是保证订单必须属于一个真实存在的客户。如果尝试给不存在的customer_id创建订单数据库会拒绝。这就是关系型数据库约束的价值把一部分数据正确性直接交给数据库守住。六、创建订单明细表 sales.order_item订单明细表保存订单里买了哪些商品、买了几件、单价是多少。订单明细表是订单和商品之间的关联载体。它既引用订单也引用商品同时保存购买数量、成交单价和行金额。这样既能还原订单内容也能支持后续按商品统计销量。CREATETABLEIFNOTEXISTSsales.order_item(item_idSERIALPRIMARYKEY,order_idINTNOTNULL,product_idINTNOTNULL,quantityINTNOTNULL,unit_priceNUMERIC(12,2)NOTNULL,line_amountNUMERIC(12,2)NOTNULL,CONSTRAINTck_order_item_quantityCHECK(quantity0),CONSTRAINTck_order_item_unit_priceCHECK(unit_price0),CONSTRAINTck_order_item_line_amountCHECK(line_amount0),CONSTRAINTfk_item_orderFOREIGNKEY(order_id)REFERENCESsales.customer_order(order_id),CONSTRAINTfk_item_productFOREIGNKEY(product_id)REFERENCESinventory.product(product_id));为什么订单明细里要保存unit_price而不是每次都从商品表查因为商品价格会变化。订单明细保存的是下单当时的成交价不能因为商品后来改价而影响历史订单。这是业务建模中非常常见的设计。七、查看所有业务表查看sales模式下的表\dt sales.*查看inventory模式下的表\dt inventory.*也可以通过 SQL 查看SELECTtable_schema,table_nameFROMinformation_schema.tablesWHEREtable_schemaIN(sales,inventory)ORDERBYtable_schema,table_name;预期结果八、给表和字段添加备注技术文章里可以不加备注但真实数据库里建议加。备注可以帮助后来的人理解对象含义。COMMENTONTABLEsales.customerISCustomer basic information;COMMENTONTABLEinventory.productISProduct and stock information;COMMENTONTABLEsales.customer_orderISOrder header table;COMMENTONTABLEsales.order_itemISOrder line item table;给关键字段加备注COMMENTONCOLUMNsales.customer.customer_levelISnormal or vip;COMMENTONCOLUMNinventory.product.stock_qtyISCurrent available stock quantity;COMMENTONCOLUMNsales.customer_order.order_statusIScreated, paid or cancelled;COMMENTONCOLUMNsales.order_item.line_amountISquantity multiplied by unit_price;查看详细结构\dsales.customer九、测试约束是否生效1. 测试手机号唯一约束先插入一条客户INSERTINTOsales.customer(customer_name,phone,email,customer_level)VALUES(张三,13800000001,zhangsanexample.com,normal);再插入相同手机号INSERTINTOsales.customer(customer_name,phone,email,customer_level)VALUES(李四,13800000001,lisiexample.com,vip);第二条会失败因为phone有唯一约束。2. 测试价格检查约束INSERTINTOinventory.product(product_code,product_name,category,unit_price,stock_qty)VALUES(P_BAD,错误价格商品,test,-10,1);这条会失败因为unit_price 0。3. 测试外键约束INSERTINTOsales.customer_order(order_no,customer_id,order_status,total_amount)VALUES(SO_BAD_001,999999,created,100);如果不存在customer_id 999999的客户这条会失败。十、常见问题排查问题 1创建订单表时报外键引用表不存在原因通常是建表顺序错了。正确顺序sales.customer inventory.product sales.customer_order sales.order_item因为订单表依赖客户表订单明细依赖订单表和商品表。问题 2表已经存在本文 SQL 使用了CREATETABLEIFNOTEXISTS...如果表已经存在通常不会重复创建。但如果你修改了表结构IF NOT EXISTS不会自动帮你改旧表。学习阶段如果想重建可以先按依赖顺序删除DROPTABLEIFEXISTSsales.order_item;DROPTABLEIFEXISTSsales.customer_order;DROPTABLEIFEXISTSinventory.product;DROPTABLEIFEXISTSsales.customer;注意删除表会删除数据执行前要确认。问题 3插入中文后显示乱码优先确认数据库编码和客户端编码。查看数据库编码\l kb_shop查看客户端编码SHOWclient_encoding;如果终端显示异常也要检查 Windows 命令行字体和代码页。十一、本文小结本文承接第四篇的 Schema 分层设计在sales和inventory模式下完成了核心业务表建模。到这里kb_shop已经从一个空业务库逐步演变成包含客户、商品、订单和订单明细的关系模型。本文完成了kb_shop的核心业务建模sales.customer 客户表 inventory.product 商品表 sales.customer_order 订单主表 sales.order_item 订单明细表并实践了SERIAL 自增主键 VARCHAR 字符字段 NUMERIC 金额字段 TIMESTAMP 时间字段 PRIMARY KEY 主键 UNIQUE 唯一约束 CHECK 检查约束 FOREIGN KEY 外键约束 COMMENT 备注下一篇会开始插入真实样例数据并用SELECT、过滤、排序、分页、聚合和多表关联查询把这些表真正用起来。第五篇解决的是“表如何设计”第六篇会继续解决“数据如何查询、统计和解释”。
