PostgreSQL / 系统课时
创建表与完整性约束
创建表与完整性约束
本课把数据模型实现为可执行的 PostgreSQL DDL,并让数据库直接阻止重复编号、非法状态、负数金额和孤立明细。完成后,你将得到一组可验证的核心表结构。
学习目标
- 使用主键、唯一、检查和外键约束表达业务规则
- 为时间、状态和数值字段设置合理默认值
- 通过失败用例验证约束是否真正生效
创建完整表结构
下面的脚本可在空数据库中直接执行。为避免与关键字冲突,订单表使用复数名 orders。
sql
CREATE TABLE customer (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_no varchar(32) NOT NULL UNIQUE,
name varchar(100) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE product (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_code varchar(32) NOT NULL UNIQUE,
name varchar(100) NOT NULL,
price numeric(12, 2) NOT NULL CHECK (price >= 0),
stock integer NOT NULL CHECK (stock >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no varchar(32) NOT NULL UNIQUE,
customer_id bigint NOT NULL REFERENCES customer(id),
status varchar(20) NOT NULL DEFAULT 'PENDING'
CHECK (status IN ('PENDING', 'PAID', 'CANCELLED')),
total_amount numeric(12, 2) NOT NULL CHECK (total_amount >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_item (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id bigint NOT NULL REFERENCES product(id),
product_name varchar(100) NOT NULL,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
UNIQUE (order_id, product_id)
);预期结果
text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE客户端可能只显示命令成功信息,不一定逐行展示;实际表现取决于使用的工具。
理解约束职责
主键保证每行可唯一定位;唯一约束保证客户号、商品编码和订单号不重复;检查约束限制金额、库存、数量和状态;外键保证订单引用真实客户,明细引用真实订单和商品。
ON DELETE CASCADE 只用于订单与明细,因为删除订单时明细失去意义。商品不使用级联删除,避免删除商品时破坏历史订单。
验证非法数据
执行下面的失败用例,确认数量检查约束生效。该语句依赖前面已存在合法订单和商品,示例中的 <ID> 替换为真实值。
sql
INSERT INTO order_item (
order_id, product_id, product_name, quantity, unit_price
) VALUES (
<ID>, <ID>, '测试商品', 0, 99.00
);预期结果
text
ERROR: new row for relation "order_item" violates check constraint约束名称可能由 PostgreSQL 自动生成,因此完整错误文本会因环境不同而略有差异。
约束不能替代全部业务逻辑
数据库可以保证单行和关联完整性,但“订单总金额必须等于所有明细之和”涉及多行计算,通常由事务写入流程维护,并通过测试或触发器额外校验。
约束应优先覆盖稳定、明确、低成本的规则。经常变化的审批流程或营销规则更适合放在应用层。
按自己的节奏,确认真正理解。