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 自动生成,因此完整错误文本会因环境不同而略有差异。

约束不能替代全部业务逻辑

数据库可以保证单行和关联完整性,但“订单总金额必须等于所有明细之和”涉及多行计算,通常由事务写入流程维护,并通过测试或触发器额外校验。

约束应优先覆盖稳定、明确、低成本的规则。经常变化的审批流程或营销规则更适合放在应用层。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建表与完整性约束

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通