KingbaseES 关系建模与完整性约束设计：以 kb_shop 为例

本文是本系列第 5 篇。前面已经创建了 kb_shop 数据库和 sales、inventory、ops 模式，本文开始创建客户、商品、订单和订单明细表。

引言

上一篇文章其实我们已经把 kb_shop 这个库里面的 sales、inventory 还有 ops 这三个模式给划分出来了。也就是说，业务对象怎么分层这个事儿算是搞定了。那么接着呢，我们就要开始搞真正的数据建模了。这个阶段通常来说要面对的问题就是，到底哪些业务实体的情况需要我们去建表？每一张表里面的话又该放哪些字段？主键这块怎么设计？还有那个表跟表之间，外键关系又是怎么建立起来的？

这篇文章咱们就围绕一个比较简单的，但是完整度够了的订单业务模型来弄。我们会去建客户表、商品表，还有那个订单主表以及订单明细表。写的时候呢，我会把建表 SQL 和关系模型这两个东西放在一起来讲。其实往往仅仅只是告诉你们“去执行这段 SQL”是不够的。那为什么要这样去拆表呢？为什么要去设置主键，还有唯一约束、检查约束以及外键约束这些个东西呢？这些原因我都会给大伙说清楚。

通常来说，你去学数据库，真正开始接触业务场景了，往往都是从建表这一步开始的。

不过建表这个事儿，它并不是说你简单把字段往那一列就完事了。你想弄出一个比较靠谱的表结构的话，通常来说你至少得回答出下面这些个问题：

1. 每一行的数据，你到底怎么去唯一标识它？
2. 哪些字段是必须要填写的，也就是不能为空的情况？
3. 又有哪些字段是不允许出现重复值的的？
4. 哪些字段里面不能塞那些非法的值进去？
5. 表跟表之间，它们究竟是一个什么样的关系？

本文会围绕 kb_shop 创建 4 张核心表：

模式	表名	用途
sales	customer	客户信息
inventory	product	商品信息和库存
sales	customer_order	订单主表
sales	order_item	订单明细

---

关系模型、主键与完整性约束

其实吧，关系型数据库最核心的东西，根本就不是说“表格长得跟Excel一样”。那是表面现象。它真正的关键点在哪呢？也就是说，得靠关系模型去把业务里的实体还有它们之间的约束给表达出来。你看啊，客户、商品，还有订单、订单明细，这几个其实就是不同的实体。那它们之间是有明确关系的。客户他会有订单，订单里面呢又包含了明细，而这个明细它又引用了商品。

那么我们在建表的时候，最要紧的事其实并不是说字段加的越多就越好，这个误区很多人都有。真正该做的事，是要把数据完整性的那些个规则给落到数据库里面去。通常来说呢，常见的完整性也就分那么三类：

类型	解决的问题	本文示例
实体完整性	每一行数据如何唯一识别	PRIMARY KEY
域完整性	某个字段允许什么值	NOT NULL、CHECK、字段类型
参照完整性	表与表之间引用是否真实存在	FOREIGN KEY

如果说这些个规则你仅仅只是在应用代码里面去写，那问题就来了。你换个导入脚本的情况，或者说换个人来维护，往往仅仅只是操作不当，就可能把规则给绕过去了。数据就出岔子了，这是一个问题。那怎么办呢？其实你只要把主键啊，唯一约束，还有检查约束以及外键约束，统统给定义在 KingbaseES 里面去。这样的话，数据库它就能变成数据质量兜底的那个东西了。

那么这篇文章里面我们建的这 4 张表，你可别觉得我是为了凑个数弄个示例出来。其实它们是构成了一个订单模型，虽然说是最小的，但是它是完整的。接着后面我们要去讲查询啊，还有事务，以及索引和备份这些个东西的时候，往往都会基于这一组关系来给大家展开说。

一、连接 kb_shop 并确认模式

进入工具目录：

cd /d D:\Tools\Kingbase\ES\Server\bin

连接 kb_shop：

ksql -U system -d kb_shop -h localhost -p 54321

确认当前数据库：

SELECT current_database() AS current_db,
       current_user AS login_user;

查看模式：

\dn

如果还没有 sales 和 inventory，先创建：

CREATE SCHEMA IF NOT EXISTS sales;
CREATE SCHEMA IF NOT EXISTS inventory;
CREATE SCHEMA IF NOT EXISTS ops;

二、业务表关系设计

那么我们先来看看逻辑关系这块。

其实在你动手去写 CREATE TABLE 之前，先把关系给理清楚，这个在数据库建模里面是很关键的一步。也就是说，表结构它不是自己孤立存在在那里的。客户啊，订单啊，还有商品，它们相互之间的关系，会直接影响到后面你的外键怎么建，索引怎么加，还有查询语句到底怎么写。那如果一开始关系就没想明白的话，后面你往往会碰到大麻烦。什么麻烦呢？很容易就会出现字段重复录入的情况，或者说产生一堆冗余数据，再或者就是统计口径乱七八糟的。

如果用一句话来概括的话，就是：

客户下订单，订单包含商品明细，商品表维护价格和库存。

这个模型的话，其实足够简单了。但是呢，它能够支撑你后面去搞很多主题的情况。你比如什么增删改查啦，还有事务扣库存啦，索引优化，视图报表，备份恢复，包括权限控制这些，都能拿它来练手。

三、创建客户表 sales.customer

客户表这个表呢，其实就是用来保存客户的一些最基础的信息的。

那么设计客户表，重点在哪里呢？其实就是两点，一个是怎么“唯一识别客户”，另一个就是怎么“控制客户状态”。在这里的话，我们是用 customer_id 来当做内部的主键。接着呢，用 phone 这个字段来当做业务上的唯一值。为什么要这么搞呢？这样搞的好处就是，既方便了数据库内部去做关联，也能避免同一个手机号被人家重复注册的情况出现。

CREATE TABLE IF NOT EXISTS sales.customer (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(80) NOT NULL,
    phone VARCHAR(30) NOT NULL,
    email VARCHAR(120),
    customer_level VARCHAR(20) NOT NULL DEFAULT 'normal',
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uk_customer_phone UNIQUE (phone),
    CONSTRAINT ck_customer_level CHECK (customer_level IN ('normal', 'vip')),
    CONSTRAINT ck_customer_status CHECK (status IN ('active', 'disabled'))
);

字段说明：

字段	类型	说明
customer_id	SERIAL	自增主键
customer_name	VARCHAR(80)	客户姓名
phone	VARCHAR(30)	手机号，不能重复
email	VARCHAR(120)	邮箱，可以留空
customer_level	VARCHAR(20)	客户等级
status	VARCHAR(20)	客户状态
created_at	TIMESTAMP	创建的时间

那么在这个表里面呢，我们一共用了 3 类约束：

约束	作用
PRIMARY KEY	用来标识每一行客户是唯一的
UNIQUE	就是限制手机号不能重复
CHECK	客户等级还有状态，只能填我们规定的那些值

接着我们来看看表结构：

\d sales.customer

四、创建商品表 inventory.product

商品表保存商品名称、价格、库存和上下架状态。

商品表承担两个角色：一是商品资料，二是库存状态。为了让后续事务扣库存更直观，本文把 stock_qty 放在商品表中演示。真实系统里库存也可能拆成独立库存表，但学习阶段这样更容易理解。

CREATE TABLE IF NOT EXISTS inventory.product (
    product_id SERIAL PRIMARY KEY,
    product_code VARCHAR(40) NOT NULL,
    product_name VARCHAR(120) NOT NULL,
    category VARCHAR(50) NOT NULL,
    unit_price NUMERIC(12,2) NOT NULL,
    stock_qty INT NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'on_sale',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uk_product_code UNIQUE (product_code),
    CONSTRAINT ck_product_price CHECK (unit_price >= 0),
    CONSTRAINT ck_product_stock CHECK (stock_qty >= 0),
    CONSTRAINT ck_product_status CHECK (status IN ('on_sale', 'off_sale'))
);

几个字段值得注意：

字段	说明
product_code	商品编码，适合对接外部系统
unit_price	使用 NUMERIC(12,2)，适合金额
stock_qty	当前库存，不能小于 0
status	商品上下架状态

金额不建议用浮点类型。这里使用 NUMERIC(12,2)，可以精确表达小数金额。

查看表结构：

\d inventory.product

五、创建订单主表 sales.customer_order

订单主表保存订单的整体信息。

订单主表只保存订单级别的信息，例如订单号、客户、状态、总金额和支付时间。它不直接保存每个商品明细，因为一个订单可能包含多个商品。把主表和明细表拆开，是典型的一对多关系设计。

CREATE TABLE IF NOT EXISTS sales.customer_order (
    order_id SERIAL PRIMARY KEY,
    order_no VARCHAR(40) NOT NULL,
    customer_id INT NOT NULL,
    order_status VARCHAR(20) NOT NULL DEFAULT 'created',
    total_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at TIMESTAMP,
    CONSTRAINT uk_customer_order_no UNIQUE (order_no),
    CONSTRAINT ck_customer_order_status CHECK (order_status IN ('created', 'paid', 'cancelled')),
    CONSTRAINT ck_customer_order_amount CHECK (total_amount >= 0),
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
        REFERENCES sales.customer (customer_id)
);

这里出现了外键：

CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
    REFERENCES sales.customer (customer_id)

它的作用是保证：订单必须属于一个真实存在的客户。

如果尝试给不存在的 customer_id 创建订单，数据库会拒绝。这就是关系型数据库约束的价值：把一部分数据正确性直接交给数据库守住。

六、创建订单明细表 sales.order_item

订单明细表保存订单里买了哪些商品、买了几件、单价是多少。

订单明细表是订单和商品之间的关联载体。它既引用订单，也引用商品，同时保存购买数量、成交单价和行金额。这样既能还原订单内容，也能支持后续按商品统计销量。

CREATE TABLE IF NOT EXISTS sales.order_item (
    item_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price NUMERIC(12,2) NOT NULL,
    line_amount NUMERIC(12,2) NOT NULL,
    CONSTRAINT ck_order_item_quantity CHECK (quantity > 0),
    CONSTRAINT ck_order_item_unit_price CHECK (unit_price >= 0),
    CONSTRAINT ck_order_item_line_amount CHECK (line_amount >= 0),
    CONSTRAINT fk_item_order FOREIGN KEY (order_id)
        REFERENCES sales.customer_order (order_id),
    CONSTRAINT fk_item_product FOREIGN KEY (product_id)
        REFERENCES inventory.product (product_id)
);

为什么订单明细里要保存 unit_price，而不是每次都从商品表查？

因为商品价格会变化。订单明细保存的是下单当时的成交价，不能因为商品后来改价而影响历史订单。

这是业务建模中非常常见的设计。

七、查看所有业务表

查看 sales 模式下的表：

\dt sales.*

查看 inventory 模式下的表：

\dt inventory.*

也可以通过 SQL 查看：

SELECT table_schema,
       table_name
FROM information_schema.tables
WHERE table_schema IN ('sales', 'inventory')
ORDER BY table_schema, table_name;

预期结果：

八、给表和字段添加备注

技术文章里可以不加备注，但真实数据库里建议加。备注可以帮助后来的人理解对象含义。

COMMENT ON TABLE sales.customer IS 'Customer basic information';
COMMENT ON TABLE inventory.product IS 'Product and stock information';
COMMENT ON TABLE sales.customer_order IS 'Order header table';
COMMENT ON TABLE sales.order_item IS 'Order line item table';

给关键字段加备注：

COMMENT ON COLUMN sales.customer.customer_level IS 'normal or vip';
COMMENT ON COLUMN inventory.product.stock_qty IS 'Current available stock quantity';
COMMENT ON COLUMN sales.customer_order.order_status IS 'created, paid or cancelled';
COMMENT ON COLUMN sales.order_item.line_amount IS 'quantity multiplied by unit_price';

查看详细结构：

\d+ sales.customer

九、测试约束是否生效

1. 测试手机号唯一约束

先插入一条客户：

INSERT INTO sales.customer (customer_name, phone, email, customer_level)
VALUES ('张三', '13800000001', 'zhangsan@example.com', 'normal');

再插入相同手机号：

INSERT INTO sales.customer (customer_name, phone, email, customer_level)
VALUES ('李四', '13800000001', 'lisi@example.com', 'vip');

第二条会失败，因为 phone 有唯一约束。

2. 测试价格检查约束

INSERT INTO inventory.product (product_code, product_name, category, unit_price, stock_qty)
VALUES ('P_BAD', '错误价格商品', 'test', -10, 1);

这条会失败，因为 unit_price >= 0。

3. 测试外键约束

INSERT INTO sales.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 使用了：

CREATE TABLE IF NOT EXISTS ...

如果表已经存在，通常不会重复创建。但如果你修改了表结构，IF NOT EXISTS 不会自动帮你改旧表。

学习阶段如果想重建，可以先按依赖顺序删除：

DROP TABLE IF EXISTS sales.order_item;
DROP TABLE IF EXISTS sales.customer_order;
DROP TABLE IF EXISTS inventory.product;
DROP TABLE IF EXISTS sales.customer;

注意：删除表会删除数据，执行前要确认。

问题 3：插入中文后显示乱码

优先确认数据库编码和客户端编码。

查看数据库编码：

\l kb_shop

查看客户端编码：

SHOW client_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、过滤、排序、分页、聚合和多表关联查询，把这些表真正用起来。第五篇解决的是“表如何设计”，第六篇会继续解决“数据如何查询、统计和解释”。