前一阵子在给一个内部业务系统做数据迁移原库跑在MySQL上表结构建得比较随意手机号没加唯一约束、金额字段没校验正负、部门删了结果员工还在引用。结果上线半年光清理脏数据就花了两周。后来整体切到OpenGauss我第一件事就是把表结构和约束重新捋了一遍。也是在这个过程中我明显感觉到很多人对OpenGauss约束的理解还停留在“加个主键就完事”的阶段其实非空、唯一、检查、外键这类“其他约束”才是真正影响数据质量的地方。这篇就围绕初识OpenGauss和添加其他约束展开适合刚开始用OpenGauss、想认真做表结构设计的同学。1. 初识OpenGauss约束是表结构设计的守门员1.1 OpenGauss到底是个什么东西OpenGauss是华为开源的数据库产品底层基于PostgreSQL内核所以很多习惯在PostgreSQL里能用的写法放到OpenGauss里基本也能跑。但它并不是简单套壳自研了SQL引擎、存储引擎、安全机制也针对企业级场景做了不少增强比如全密态、账本数据库、资源池化这些能力。当前在政务、金融、运营商等领域已经有不少落地案例普通中小型项目的使用也越来越多。从使用者的角度看OpenGauss和MySQL、Oracle最大的区别在于它属于集中式数据库里偏企业级的那一路语法上更接近Oracle和PostgreSQL的融合体同时保留了完整的事务、约束、外键等标准能力。默认情况下安装完数据库之后可以通过gsql命令行工具连接提示符常显示为openGauss#熟悉命令行操作的话上手很快。也正因为兼容PostgreSQL生态很多在PG上积累的建表经验和约束设计思路可以直接迁移过来学习成本比想象中低。1.2 为什么我把约束当作表设计的头等大事数据质量问题的根源绝大多数不是应用代码写错而是表结构缺少约束。举个最常见的例子用户注册接口里代码里明明做了手机号唯一性的判断但并发请求一多两个请求同时读到“手机号未被占用”然后同时写入就产生了重复手机号。这时候如果表上有唯一约束数据库会直接拒绝第二条写入如果没加只能等到跑数的时候发现数据脏了再回头清理。约束就是数据库层面的“守门员”。应用层逻辑纵有千层套路绕过了约束检查数据就能进表而一旦约束生效任何非法数据都会被拦在门外。OpenGauss和其他主流数据库一样把约束作为SQL标准能力内置在引擎里可以在建表时定义也可以在表存在后用ALTER TABLE动态添加。我后面聊的“添加其他约束”指的就是主键约束之外的那几类NOT NULL、UNIQUE、CHECK、FOREIGN KEY。它们各自解决不同方向的数据问题组合起来才能构成一张完整的安全网。2. 约束类型不复杂难的是选型思路2.1 五类常用约束的定位OpenGauss常用的约束类型其实和大多数数据库差不多一张表可以同时叠加多种约束。先用一张表把它们的核心差异看清楚约束类型作用底层实现典型业务场景NOT NULL字段不允许为NULL列属性几乎无额外开销姓名、订单号、金额等必填字段UNIQUE字段或字段组合的值不允许重复自动创建唯一索引手机号、邮箱、业务流水号PRIMARY KEY非空且唯一一张表一般一个自动创建主键索引每张表的唯一标识字段CHECK字段值必须满足条件表达式插入更新时做表达式计算状态枚举、取值范围、格式校验FOREIGN KEY字段值必须存在于被引用表的对应键中DML时检查引用关系子表关联父表的业务主数据NOT NULL在OpenGauss里其实算一种列约束它不是记录在约束系统表里的独立对象而是存在列属性上所以开销最小但约束力也最直接字段一旦设为NOT NULL写入NULL就直接报错。UNIQUE约束背后会创建一个唯一索引它不仅保证数据不重复还能加速按该字段查询的速度。PRIMARY KEY可以理解为NOT NULL和UNIQUE的叠加但它在一个表里通常只允许一个而UNIQUE约束可以有多个。CHECK约束是最灵活的规则定义方式凡是能在表达式里写出来的条件都能用来校验数据。比如金额大于等于0、年龄在某个区间、状态字段只能取几个固定值。FOREIGN KEY约束用来建立表与表之间的引用关系它要求当前字段的值必须已存在于被引用表的键值里否则拒绝写入。它是防止“孤儿数据”的关键手段。2.2 一个业务字段该用哪种约束怎么判断刚接触数据库的人最容易纠结的问题是这个字段到底该加什么约束我的判断方法很简单先想这个字段的数据被写坏会有什么后果再倒推需要什么约束。以用户表为例。手机号这个字段如果业务上规定一个用户只能绑定一个手机号那么它必须是非空且唯一的对应NOT NULL加UNIQUE。邮箱字段如果允许用户不填那么就不能加NOT NULL但一旦填写就不能和别人重复所以只加UNIQUE。用户状态字段可能取值只有“正常”和“停用”这类字段加CHECK约束最合适写法是CHECK (status IN (N, D))比在应用层每次判断状态值省心得多。金额字段必须大于等于0就写CHECK (amount 0)绝对不要指望每个开发在写代码时都记得判断负数。外键约束的判断稍复杂一点。比如订单表的用户ID它引用用户表的用户ID从数据完整性角度出发应该加外键。但有些高并发系统考虑到外键在每次写入时都要去父表做引用检查担心性能开销会在表上只建立普通索引靠应用逻辑保证引用关系。这个取舍没有绝对对错要看团队对数据一致性的容忍度。我的建议是核心交易链路上的引用关系外键一定要加非核心日志类、临时表的引用关系可以灵活处理。2.3 约束不是越多越好我见过一种反向操作有人为了保证“数据绝对干净”把所有能想到的约束全堆到表上结果插入性能明显下降开发改数据也处处碰壁。约束当然有代价而且每个约束的代价类型不一样。唯一约束的代价最直接每次插入或更新都要查唯一索引数据的写入速度会受影响唯一索引越多写入链路越长。外键约束的代价在于每次DML都会触发对父表的引用检查如果父表这一行刚好还在高频更新就可能产生额外的锁等待。CHECK约束的代价是表达式计算虽然单个表达式很快但表上CHECK多了插入更新时校验的计算量也会线性增加。NOT NULL的代价几乎可以忽略因为它只判断一次是否为NULL。所以我的原则是必填字段一定加NOT NULL唯一业务标识一定加UNIQUE取值范围明确的字段加CHECK核心引用关系加外键但绝不为了“规范”堆无效约束。一个字段如果允许NULL且无重复要求就不要硬加约束一个状态字段如果业务上随时可能扩展取值CHECK约束反而会变成改表的负担这时也许只用NOT NULL加注释说明更实际。3. 建表阶段把约束写对后面少踩一半坑3.1 列级约束写法在OpenGauss里建表时最直接的写法是把约束直接写在字段后面这叫列级约束。下面用部门和员工两张表演示CREATE TABLE department ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) NOT NULL, manager_name VARCHAR(30) ); CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_no VARCHAR(20) NOT NULL UNIQUE, emp_name VARCHAR(50) NOT NULL, dept_id INT NOT NULL REFERENCES department(dept_id), salary NUMERIC(10,2) CHECK (salary 0), gender CHAR(1) CHECK (gender IN (M, F)) );这段SQL里有几个细节值得解释。emp_no VARCHAR(20) NOT NULL UNIQUE表示员工编号既不能为空也不能重复这是员工表里非常重要的唯一标识。dept_id INT NOT NULL REFERENCES department(dept_id)表示部门ID不能为空并且必须存在于department表的dept_id列中。salary NUMERIC(10,2) CHECK (salary 0)和gender CHAR(1) CHECK (gender IN (M, F))都是范围校验前者保证工资不为负数后者保证性别只能取规定的两个值。列级约束的优点是直观建表语句读起来一目了然。但缺点也明显如果一张表有几十个字段每个字段后面都堆一堆约束关键字整段SQL会显得很长而且列级约束无法表示多个字段联合的唯一性或联合检查。这时就要用到表级约束。3.2 表级约束和复合约束表级约束写在所有字段定义之后重点是能处理多字段组合的约束。比如员工表里如果业务规定“同一个员工编号不能同时出现在两个部门”那就要对(dept_id, emp_no)建联合唯一约束。这个诉求用列级约束实现不了只能通过表级约束写CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_no VARCHAR(20) NOT NULL, emp_name VARCHAR(50) NOT NULL, dept_id INT NOT NULL REFERENCES department(dept_id), salary NUMERIC(10,2), CONSTRAINT uk_employee_dept_emp UNIQUE (dept_id, emp_no) );这个uk_employee_dept_emp约束创建了一个基于两个字段的联合唯一索引效果是允许单个字段重复比如dept_id可以重复emp_no也可以重复但两者组合不能重复。这种约束在业务系统里非常常见比如订单明细表里同一个订单不能出现两条相同商品的记录就可以用UNIQUE (order_id, product_id)。表级约束也能写CHECK。比如员工年龄和工龄之间有业务规则可以用CHECK (age work_age 18)这样的表达式列级约束无法引用其他字段表级约束就可以。与之类似的还有复合主键比如明细表的PRIMARY KEY (order_id, item_no)同样只能写成表级约束。3.3 约束命名给自己留条后路建表时不写约束名OpenGauss会自动生成默认名比如employee_emp_no_key、employee_salary_check、employee_dept_id_fkey。这样的名字虽然能看出是哪张表哪列但在报错信息里可读性很差。比如生产环境报一个ERROR: duplicate key value violates unique constraint employee_emp_no_key一眼还能猜出是员工编号重复了。可如果约束多了或者系统自动生成的名字不够直观排查起来就要来回翻表结构。我自己习惯在建表时显式命名约束规则很简单主键用pk_开头唯一约束用uk_开头检查约束用ck_开头外键约束用fk_开头后面接表名和相关字段。比如CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_no VARCHAR(20) NOT NULL, emp_name VARCHAR(50) NOT NULL, dept_id INT NOT NULL, salary NUMERIC(10,2), CONSTRAINT pk_employee PRIMARY KEY (emp_id), CONSTRAINT uk_employee_emp_no UNIQUE (emp_no), CONSTRAINT ck_employee_salary CHECK (salary 0), CONSTRAINT fk_employee_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id) );这样做的实际收益是当数据库报错时错误信息直接告诉你违反的约束叫什么、约束涉及哪些字段定位问题的速度快很多。另一个好处是后续如果业务规则变了想删除某个约束可以按名字精准操作不用先去系统表里查默认名。4. 上线后补约束ALTER TABLE也能救急4.1 ALTER TABLE添加约束的语法现实开发里很多人是表上线跑了一两个月之后才意识到约束少了比如重复数据已经出现才发现当初忘了加唯一约束。好在OpenGauss支持用ALTER TABLE在表存在后动态添加约束不需要重建表。几种常见的添加方式如下-- 添加唯一约束 ALTER TABLE employee ADD CONSTRAINT uk_employee_email UNIQUE (email); -- 添加检查约束 ALTER TABLE employee ADD CONSTRAINT ck_employee_age CHECK (age 18 AND age 65); -- 添加外键约束 ALTER TABLE employee ADD CONSTRAINT fk_employee_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id); -- 将字段改为非空 ALTER TABLE employee ALTER COLUMN emp_name SET NOT NULL;这里要特别提醒给字段设置NOT NULL用ALTER COLUMN ... SET NOT NULL不是ADD CONSTRAINT NOT NULL。这是初学者最容易搞混的地方因为其他约束类型都可以用ADD CONSTRAINT唯独NOT NULL是列属性语法不一样。4.2 添加约束前必须处理的历史数据动态添加约束的一个隐藏前提是表里已有的数据必须全部满足新约束否则ALTER TABLE会直接失败。这是很多人踩坑的重灾区。举个例子用户表里已经存在两行相同邮箱的数据这时你执行ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);数据库会报类似could not create unique index的错误并不是语法有问题而是存量数据不符合唯一性要求。添加CHECK约束也一样假如表里有负数金额再执行CHECK (amount 0)就会失败。所以生产环境补约束前一定要先查存量数据。查重复可以这样写SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) 1;查NULL值可以这样写SELECT COUNT(*) FROM users WHERE email IS NULL;查外键引用是否对得上可以用LEFT JOIN去比对子表字段在父表里是否存在。处理完存量问题再执行添加约束的操作。顺序一定是先查后加不要抱着侥幸心理直接执行。4.3 删除约束与约束调整约束加多了想删除用DROP CONSTRAINT就行ALTER TABLE employee DROP CONSTRAINT uk_employee_email; ALTER TABLE employee DROP CONSTRAINT fk_employee_dept;删除约束时要注意两点。第一被外键约束引用的父表主键或唯一键如果存在依赖关系直接删除可能被拒绝或连带影响子表的约束操作前要确认没有其他对象依赖它。第二删除唯一约束和主键约束后对应的唯一索引通常也会一并删除但如果是手动创建的唯一索引则需要单独DROP INDEX。业务规则经常变化约束不可能一动不动。比如某个状态字段当初用CHECK限定了只能取A和B后来业务要增加一个C状态必须先删除旧CHECK约束再添加包含C的新CHECK约束没有直接“修改CHECK表达式”的语法。这个流程在测试环境多演练几遍减少生产操作失误的概率。5. 一个订单系统的约束设计实战5.1 业务需求和表关系约束设计不能只聊理论我拿一个最常见的订单系统来演示。系统涉及四张表用户表、商品表、订单表、订单明细表。它们之间的关系是一个用户有多张订单一张订单包含多条订单明细一条明细对应一个商品。这个结构几乎覆盖了所有约束类型。先梳理关键规则用户手机号必填且唯一状态只能是正常或停用商品名称必填价格不能为负订单号必填且唯一订单状态只能取固定枚举值订单总金额不能为负订单明细必须挂在已存在的订单上商品必须存在于商品表购买数量必须大于0明细价格不能为负5.2 完整建表SQL根据上述规则建表SQL如下CREATE TABLE users ( user_id INT PRIMARY KEY, mobile VARCHAR(20) NOT NULL, email VARCHAR(100), status CHAR(1) DEFAULT 0 CHECK (status IN (0, 1)), create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_users_mobile UNIQUE (mobile) ); CREATE TABLE product ( product_id INT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price NUMERIC(10,2) CHECK (price 0) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT NOT NULL REFERENCES users(user_id), order_no VARCHAR(30) NOT NULL, status VARCHAR(20) NOT NULL CHECK (status IN (pending, paid, shipped, done, cancel)), total_amount NUMERIC(12,2) NOT NULL CHECK (total_amount 0), order_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_orders_order_no UNIQUE (order_no) ); CREATE TABLE order_item ( item_id INT PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(order_id), product_id INT NOT NULL REFERENCES product(product_id), quantity INT NOT NULL CHECK (quantity 0), price NUMERIC(10,2) NOT NULL CHECK (price 0), CONSTRAINT uk_order_item UNIQUE (order_id, product_id) );这张订单系统里orders表的user_id引用users表的用户ID保证订单不会落到不存在的人头上order_item表的order_id引用orders表的订单ID保证明细不会变成孤儿数据product_id引用商品表保证买的商品一定存在。uk_order_item这条联合唯一约束则保证同一张订单里不会重复购买同一件商品。这个建表脚本看起来很简单但它已经把NOT NULL、UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY五种约束全用上了。把这样的脚本放到测试库里跑一遍再用几条非法的INSERT语句去验证约束生效情况比自己纸上谈兵十遍都管用。5.3 约束设计里的取舍这个订单系统如果继续优化有几个地方值得反复权衡。第一外键要不要加ON DELETE CASCADE。默认情况下父表有子表引用时删除父表数据会报外键冲突。如果需要级联删除可以在外键定义里加ON DELETE CASCADE比如删订单时自动删明细。这个功能很方便但风险也大一旦误删父表数据子表数据会被连带清空。所以我更倾向于用ON DELETE RESTRICT保住最后一道防线删除操作交给业务代码有意识地控制。第二唯一约束和索引的重复。上面uk_orders_order_no会生成唯一索引如果业务上还需要频繁用order_no做查询这个索引顺带覆盖了查询需求不需要额外建索引。反过来如果为了查询速度手动建了一个普通索引再添加唯一约束时又建一个唯一索引就会造成索引冗余浪费存储空间也拖慢写入。第三CHECK约束不要写太长太复杂的表达式。比如那种一长串条件拼接的CHECK维护起来非常痛苦而且表达式里不能包含子查询、不能调用某些特殊函数。复杂业务规则更适合用触发器或存储过程去控制CHECK适合做静态的、可枚举的、简单的规则。OpenGauss本身对存储过程支持不错我在实际项目中经常采用“CHECK管基础规则触发器管复杂联动”的分层方案。6. 约束相关的常见报错和排查实录6.1 四条典型约束错误对照约束加好了实际写入数据时就会遇到各种报错。我整理了一份最常用的对照表基本覆盖了日常开发里九成以上的约束问题错误类型典型错误信息触发原因处理思路唯一约束冲突duplicate key value violates unique constraint uk_users_mobile插入或更新时产生了重复值先查存量重复数据再让应用处理冲突逻辑非空约束冲突null value in column status violates not-null constraint往NOT NULL字段写入NULL检查写入语句给字段补齐默认值或合法值外键约束冲突insert or update on table order_item violates foreign key constraint order_item_order_id_fkey子表引用了父表中不存在的键值检查父表数据是否存在或者修正插入的引用值检查约束冲突new row for relation orders violates check constraint orders_total_amount_check写入的值不满足CHECK表达式按CHECK规则修改写入数据看到这些报错不要慌错误信息里通常直接标出了约束名。约束名起得规范的话马上就知道是哪个表哪个字段出了问题。6.2 一次添加唯一约束失败的排查过程真实环境里动态加约束遇到的坑远比自己建表时多。我印象很深的一次是给一张将近50万行的用户表加手机号唯一约束执行SQL后直接报错。当时报错信息没有明确指到具体重复数据只提示无法创建唯一索引。我当时按这个顺序排查先查重复手机号执行分组统计SELECT mobile, COUNT(*) FROM users GROUP BY mobile HAVING COUNT(*) 1;结果发现了几十组重复数据数量不大但足够让约束创建失败。接下来的问题是保留哪一条记录、其余怎么处理。我按业务规则确认了每组里保留最新注册的那条再把旧的重复记录更新成带后缀的临时手机号。处理完后再执行ALTER TABLE约束就顺利加上了。这个案例给我一个很重要的教训对已有表添加约束之前不要只看SQL语法SQL语法没问题不代表数据没问题。存量数据就是约束创建路上最大的变量。6.3 查看约束信息的两种方式日常开发里经常要确认一张表到底有哪些约束两种方式我最常用。第一种是用gsql的元命令直接查看表结构\d users这个命令会把表的所有字段、类型、非空属性、默认值以及主键、唯一、检查、外键约束全部列出来信息非常全。缺点是在脚本或程序里不好用。第二种是查询系统表pg_constraintSELECT conname, contype, pg_get_constraintdef(oid) AS definition FROM pg_constraint WHERE conrelid users::regclass;这里contype字段有几种取值p代表主键u代表唯一约束c代表检查约束f代表外键约束。要注意的是NOT NULL约束不记录在pg_constraint里它存储在pg_attribute的attnotnull字段中。想查某张表哪些字段是NOT NULL可以执行SELECT attname, attnotnull FROM pg_attribute WHERE attrelid users::regclass AND attnum 0 AND NOT attisdropped;我一般在写自动化脚本时用第二种方式因为可以精确拿到约束定义判断某个约束是否存在比人工看命令行输出靠谱得多。7. 用了大半年OpenGauss之后想补充的经验约束这件事说起来是建表语句里的几个关键词但真正把它用明白需要的是对整个数据生命周期的理解。我在OpenGauss上蹚过一些坑之后有几个细节特别想分享。第一个是约束命名规范一定要在项目初期就定好。团队里十几个人如果每个人都按自己的习惯写约束后期报错信息会非常混乱。统一用pk_、uk_、ck_、fk_前缀成本几乎为零收益却立竿见影。第二个是任何对存量表加约束的操作都先在一个克隆环境或备份环境里验证一遍。生产表几十万上百万行数据时加唯一约束和加外键约束的时间可能超出预期而且出错回滚也可能影响线上服务。我在一个核心表上加外键约束时就遇到过数据库会话一直不结束的情况后来才发现是某个长事务锁住了父表。先确认没有长事务、没有锁等待再执行ALTER TABLE会稳妥很多。第三个印象是OpenGauss的几个“脾气”。比如gsql里长时间不操作会话可能被服务端空闲超时机制自动断开连数据库时会看到类似session unused timeout的提示这其实是正常回收不算故障。另外OpenGauss对约束的报错信息和PostgreSQL高度相似拿PG的排查经验套到OpenGauss上基本是通的这算是迁移时的一个小利好。最后想说的是约束设计没有标准答案但底线很明确核心业务表的关键字段必须有约束托底。手机号该唯一的必须唯一金额该非负的必须非负订单明细该挂订单的必须挂订单。把这块做扎实后面做数据清洗、报表分析、系统迁移的时候你会感谢当初那个认真写约束的自己。