OpenGauss 表约束及字符集校对集详解

一、OpenGauss 表的约束（完整性规则）

约束是数据库保证数据正确性、一致性的核心机制，OpenGauss 支持三大类完整性约束，下面先解释概念，再给出具体创建示例。

1. 域完整性（列级完整性）

  • 概念：限制列的取值范围和格式，确保列中的数据符合定义的规则（比如非空、默认值、数据类型、取值范围等），是对单个列的约束。

  • 作用：防止无效、不合理的数据存入某一列（比如年龄不能为负数、手机号格式正确）。

2. 实体完整性（行级完整性）

  • 概念：保证表中每行数据都是唯一的、可识别的，核心是主键（Primary Key）约束，主键列不能重复且不能为 NULL。

  • 作用：避免表中出现重复记录，确保每行数据都有唯一标识（比如用户表的用户 ID 不能重复）。

3. 参照完整性（表间完整性）

  • 概念：保证两个表之间的关联数据一致，核心是外键（Foreign Key）约束，外键列的值必须是关联表主键列的有效值（或 NULL）。

  • 作用：防止出现“孤儿数据”（比如订单表的用户 ID 必须是用户表中存在的 ID）。

二、各类约束的创建示例

准备工作：先创建测试库/模式（可选）

-- 创建测试数据库（如果需要）
CREATE DATABASE test_db;
\c test_db; -- 切换到测试库

-- 创建模式（可选）
CREATE SCHEMA test_schema;
SET search_path TO test_schema;

1. 默认值约束（域完整性）

  • 作用：列未显式赋值时，自动使用默认值。

  • 示例：创建用户表，register_time 默认为当前时间，status 默认为 1（启用）。

CREATE TABLE t_user (
    user_id INT,
    user_name VARCHAR(50),
    register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 默认当前时间
    status INT DEFAULT 1 -- 默认值 1
);

-- 测试：插入数据时不指定默认值列
INSERT INTO t_user (user_id, user_name) VALUES (1, '张三');

-- 查询验证：register_time 会自动填充当前时间，status 为 1
SELECT * FROM t_user;

2. 非空约束（域完整性）

  • 作用：强制列的值不能为 NULL。

  • 示例：修改/创建表，user_name 非空，phone 非空。

-- 方式1：创建表时指定
CREATE TABLE t_user (
    user_id INT,
    user_name VARCHAR(50) NOT NULL, -- 非空约束
    phone VARCHAR(20) NOT NULL,     -- 非空约束
    register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 方式2：已有表添加非空约束
ALTER TABLE t_user ADD CONSTRAINT ck_user_phone_notnull CHECK (phone IS NOT NULL);

-- 测试：插入 NULL 值会报错
INSERT INTO t_user (user_id, user_name, phone) VALUES (2, NULL, '13800138000'); -- 报错：user_name 非空

3. 唯一约束（实体完整性）

  • 作用：保证列（或列组合）的值唯一（可包含 NULL，但 NULL 仅能出现一次）。

  • 示例：phone 列唯一（手机号不能重复），user_name + email 组合唯一。

-- 方式1：创建表时指定单列唯一
CREATE TABLE t_user (
    user_id INT,
    user_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE, -- 唯一约束
    email VARCHAR(50),
    -- 组合唯一约束
    CONSTRAINT uk_user_name_email UNIQUE (user_name, email)
);

-- 方式2：已有表添加唯一约束
ALTER TABLE t_user ADD CONSTRAINT uk_user_phone UNIQUE (phone);

-- 测试：插入重复手机号会报错
INSERT INTO t_user (user_id, user_name, phone) VALUES (1, '张三', '13800138000');
INSERT INTO t_user (user_id, user_name, phone) VALUES (2, '李四', '13800138000'); -- 报错：phone 重复

4. 主键约束（实体完整性）

  • 作用：唯一标识行，同时满足“非空 + 唯一”，一个表只能有一个主键（可组合主键）。

  • 示例：user_id 作为主键（单列主键），订单表 order_id + shop_id 组合主键。

-- 方式1：单列主键（推荐）
CREATE TABLE t_user (
    user_id INT PRIMARY KEY, -- 主键约束（自动非空+唯一）
    user_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE
);

-- 方式2：组合主键
CREATE TABLE t_order (
    order_id INT,
    shop_id INT,
    order_time TIMESTAMP,
    -- 组合主键
    PRIMARY KEY (order_id, shop_id)
);

-- 方式3：已有表添加主键
ALTER TABLE t_user ADD CONSTRAINT pk_user_id PRIMARY KEY (user_id);

-- 测试：主键重复/NULL 都会报错
INSERT INTO t_user (user_id, user_name, phone) VALUES (1, '张三', '13800138000');
INSERT INTO t_user (user_id, user_name, phone) VALUES (1, '李四', '13900139000'); -- 报错：主键重复

5. 外键约束（参照完整性）

  • 作用：关联两个表，外键列引用主表的主键/唯一键，保证数据关联一致。

  • 示例：订单表 t_order 的 user_id 引用用户表 t_user 的 user_id。

-- 先创建主表（用户表）
CREATE TABLE t_user (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL
);

-- 创建从表（订单表），添加外键约束
CREATE TABLE t_order (
    order_id INT PRIMARY KEY,
    order_amount NUMERIC(10,2),
    user_id INT,
    -- 外键约束：user_id 引用 t_user.user_id
    CONSTRAINT fk_order_user_id FOREIGN KEY (user_id) 
        REFERENCES t_user (user_id)
        ON DELETE SET NULL -- 主表记录删除时，从表外键列设为 NULL
        ON UPDATE CASCADE  -- 主表主键更新时，从表外键列同步更新
);

-- 测试1：插入合法外键值（正常）
INSERT INTO t_user (user_id, user_name) VALUES (1, '张三');
INSERT INTO t_order (order_id, order_amount, user_id) VALUES (1001, 99.9, 1);

-- 测试2：插入不存在的 user_id（报错）
INSERT INTO t_order (order_id, order_amount, user_id) VALUES (1002, 59.9, 999); -- 报错：外键约束失败

三、字符集与校对集（排序规则）

1. 基本概念

  • 字符集（Character Set）：定义数据库/表/列支持的字符编码（比如 UTF8、GBK），决定能存储哪些字符（中文、英文、特殊符号等）。

OpenGauss 主流使用 UTF8（兼容所有语言），也支持 GBK（简体中文）、SQL_ASCII 等。

  • 校对集（Collation）：定义字符的排序、比较规则（比如大小写是否敏感、中文按拼音/笔画排序）。

格式：字符集_地区_排序规则（比如 zh_CN.UTF8 表示中文 UTF8 编码，按拼音排序）。

2. 设置方式（优先级：列 > 表 > 数据库 > 实例）

（1）实例级设置（修改配置文件，需重启）
# 编辑 openGauss 配置文件（postgresql.conf）
vi $GAUSSDATA/postgresql.conf

# 设置默认字符集和校对集
server_encoding = 'UTF8'          # 默认字符集
collation_server = 'zh_CN.UTF8'   # 默认校对集（中文拼音排序）

# 重启数据库生效
gs_om -t stop && gs_om -t start
（2）数据库级设置（创建/修改数据库）
-- 创建数据库时指定字符集和校对集
CREATE DATABASE test_db 
    WITH ENCODING = 'UTF8'       -- 字符集
    LC_COLLATE = 'zh_CN.UTF8'    -- 排序规则（校对集）
    LC_CTYPE = 'zh_CN.UTF8';     -- 字符分类规则（通常和 LC_COLLATE 一致）

-- 修改已有数据库的校对集（需谨慎，仅空库/低负载时操作）
ALTER DATABASE test_db SET LC_COLLATE = 'zh_CN.UTF8';
（3）表级/列级设置
-- 表级设置（列继承表的规则，可单独覆盖）
CREATE TABLE t_user (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL,
    -- 列级指定校对集（大小写不敏感）
    email VARCHAR(50) COLLATE "zh_CN.UTF8"
) WITH (ENCODING = 'UTF8');

-- 测试校对集：中文按拼音排序
INSERT INTO t_user (user_id, user_name) VALUES (1, '张三'), (2, '李四'), (3, '王五');
SELECT * FROM t_user ORDER BY user_name COLLATE "zh_CN.UTF8"; -- 按拼音：李四、王五、张三

3. 常用校对集示例

校对集 说明
zh_CN.UTF8 中文 UTF8，拼音排序
zh_CN.UTF8_BIN 中文 UTF8，二进制排序（大小写敏感）
en_US.UTF8 英文 UTF8，默认排序
C（或 POSIX） 二进制排序，最快，大小写敏感

总结

  1. 三大完整性约束：域完整性（列规则，如非空/默认值）、实体完整性（行唯一，如主键/唯一约束）、参照完整性（表关联，如外键）。

  2. 约束创建：可在 CREATE TABLE 时直接定义，也可通过 ALTER TABLE 给已有表添加，主键/外键建议显式命名约束名。

  3. 字符集与校对集：OpenGauss 推荐用 UTF8 字符集，校对集决定排序/比较规则，优先级：列 > 表 > 数据库 > 实例。

（注：）

©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

  • 2018-07-18 字符的基础操作
    """1.个性化消息: 将用户的姓名存到一个变量中，并向该用户显示一条消息。显示的消息应非常简单，如“Hello ...
    她即我命阅读 5,859评论 0赞 6
  • python遇到的错误
    1、expected an indented block 冒号后面是要写上一定的内容的（新手容易遗忘这一点）； 缩...
    庵下桃花仙阅读 1,159评论 1赞 2
  • 2018-07-18
    一、工具箱(多种工具共用一个快捷键的可同时按【Shift】加此快捷键选取)矩形、椭圆选框工具 【M】移动工具 【V...
    墨雅丫阅读 1,827评论 0赞 0
  • 学习的反思(上)
    跟随樊老师和伙伴们一起学习心理知识提升自已，已经有三个月有余了，这一段时间因为天气的原因休课，顺便整理一下之前学习...
    学习思考行动阅读 1,191评论 0赞 2
  • 爱，一直在
    一脸愤怒的她躺在了床上，好几次甩开了他抱过来的双手，到最后还坚决的翻了个身，只留给他一个冷漠的背影。 多次尝试抱她...
    海边的蓝兔子阅读 1,084评论 1赞 4

友情链接更多精彩内容