--- title: OceanBase 数据库中唯一性约束与普通约束的区别-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于OceanBase 数据库中唯一性约束与普通约束的区别相关的常见问题和使用技巧，帮助您快速解决OceanBase 数据库中唯一性约束与普通约束的区别的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # OceanBase 数据库中唯一性约束与普通约束的区别 更新时间：2026-05-21 01:56 适用版本： V2.1.x、V2.2.x、V3.1.x、V3.2.x 内容类型：TechNote 本文主要介绍 OceanBase 数据库中唯一性约束和其它几类普通约束（NOT NULL 约束、外键约束、Check 约束）的区别。 OceanBase 数据库支持多种类型的完整性约束。 用户可以使用以下完整性约束对输入的列值加以限制： - NOT NULL 完整性约束 - UNIQUE KEY 完整性约束 - PRIMARY KEY 完整性约束 - 引用完整性约束 这边看上去 UNIQUE 唯一性约束跟其它几类普通约束（NOT NULL 约束、外键约束、Check 约束）并无明显的区别，但是由于在 OceanBase 数据库中 UNIQUE 约束是通过索引来实现的，唯一性约束本质上是一个唯一性索引，因此，OceanBase 数据库的 UNIQUE 唯一性约束和其它几类普通约束有以下几个重大的区别。 1. 添加唯一性约束时，不支持指定 enable/validate/rely 等状态。 ```shell obclient [SYS]> ALTER TABLE t1 ADD CONSTRAINT cst UNIQUE(name) ENABLE NOVALIDATE; ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'ENABLE NOVALIDATE' at line 1 ``` 而其它类型的约束都是支持的。 ```shell obclient [SYS]> ALTER TABLE t1 ADD CONSTRAINT cst1 check(name != '123') ENABLE NOVALIDATE; Query OK, 0 rows affected (0.037 sec) ``` 2. 添加唯一性约束后，不支持 disable 唯一性约束，同时 MySQL 模式下也不支持 drop 唯一性约束，只能 drop 对应的索引（与原生的 MySQL 5.x 版本行为保持一致）。 ```shell obclient [test]> alter table t1 add constraint const1 unique(c1); obclient [test]> alter table t1 drop constraint const1; ERROR 3940 (HY000): Constraint 'const1' does not exist. ``` Oracle 模式下为保持与原生 Oracle 模式的兼容，支持 drop 唯一性约束。 ```shell obclient [SYS]> alter table t1 add constraint const1 unique(id); Query OK, 0 rows affected (1.384 sec) obclient [SYS]> alter table t1 drop constraint const1; Query OK, 0 rows affected (0.066 sec) ``` 3. 添加一个唯一性约束后，会自动添加一个同名的索引；反之，添加一个唯一索引，也会自动添加一个同名的唯一性约束。 ```shell obclient [SYS]> alter table t1 add constraint const1 unique(name); Query OK, 0 rows affected (0.544 sec) ``` ```shell obclient [SYS]> select * from all_constraints where owner='SYS' and table_name='T1'\G *************************** 1. row *************************** OWNER: SYS CONSTRAINT_NAME: CONST1 CONSTRAINT_TYPE: U <== 这个表示是唯一性约束！ TABLE_NAME: T1 SEARCH_CONDITION: NULL R_OWNER: NULL R_CONSTRAINT_NAME: NULL DELETE_RULE: NULL STATUS: ENABLED DEFERRABLE: NOT DEFERRABLE DEFERRED: IMMEDIATE VALIDATED: VALIDATED GENERATED: NULL BAD: NULL RELY: NULL LAST_CHANGE: NULL INDEX_OWNER: SYS INDEX_NAME: CONST1 <== Oracle模式下唯一约束会对应一个同名的唯一索引！ INVALID: NULL VIEW_RELATED: NULL ``` ```shell obclient [SYS]> alter table t1 drop constraint const1; Query OK, 0 rows affected (0.035 sec) ``` 也可以直接 drop 对应的唯一索引。 ```shell obclient [SYS]> drop index CONST1; Query OK, 0 rows affected (0.029 sec) ``` ```shell obclient [SYS]> create unique index idx1 on t1(name); Query OK, 0 rows affected (0.539 sec) ``` ```shell obclient [SYS]> select * from all_constraints where owner='SYS' and table_name='T1'\G *************************** 1. row *************************** OWNER: SYS CONSTRAINT_NAME: IDX1 <== Oracle模式下唯一索引会对应一个同名的唯一约束！ CONSTRAINT_TYPE: U <== 这个表示是唯一性约束！ TABLE_NAME: T1 SEARCH_CONDITION: NULL R_OWNER: NULL R_CONSTRAINT_NAME: NULL DELETE_RULE: NULL STATUS: ENABLED DEFERRABLE: NOT DEFERRABLE DEFERRED: IMMEDIATE VALIDATED: VALIDATED GENERATED: NULL BAD: NULL RELY: NULL LAST_CHANGE: NULL INDEX_OWNER: SYS INDEX_NAME: IDX1 INVALID: NULL VIEW_RELATED: NULL ``` ```shell obclient [SYS]> drop index idx1; Query OK, 0 rows affected (0.028 sec) ``` 也可以直接 drop 对应的唯一约束。 ```shell obclient [SYS]> alter table t1 drop constraint idx1; Query OK, 0 rows affected (0.034 sec) ``` ```shell obclient [test]> alter table t1 add constraint const1 unique(name); Query OK, 0 rows affected (4.411 sec) ``` ```shell obclient [test]> select * from information_schema.table_constraints where table_name='t1'; +--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED | +--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+ | def | test | const1 | test | t1 | UNIQUE | YES | +--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+ 1 row in set (0.006 sec) ``` ```shell obclient [test]> show index from t1\G *************************** 1. row *************************** Table: t1 Non_unique: 0 Key_name: const1 <== MySQL模式下唯一约束会对应一个同名的唯一索引！ Seq_in_index: 1 Column_name: name Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: available Index_comment: Visible: YES 1 row in set (0.004 sec) ``` ```shell obclient [test]> alter table t1 drop constraint const1; ERROR 3940 (HY000): Constraint 'const1' does not exist. obclient [test]> alter table t1 drop index const1; Query OK, 0 rows affected (0.082 sec) ``` ```shell obclient [test]> create unique index idx1 on t1(name); Query OK, 0 rows affected (5.158 sec) ``` ```shell obclient [test]> show index from t1\G *************************** 1. row *************************** Table: t1 Non_unique: 0 Key_name: idx1 Seq_in_index: 1 Column_name: name Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: available Index_comment: Visible: YES 1 row in set (0.003 sec) ``` ```shell obclient [test]> select * from information_schema.table_constraints where table_name='t1'\G *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: test CONSTRAINT_NAME: idx1 <== MySQL模式下唯一索引会对应一个同名的唯一约束！ TABLE_SCHEMA: test TABLE_NAME: t1 CONSTRAINT_TYPE: UNIQUE <== 这个表示是唯一性约束！ ENFORCED: YES 1 row in set (0.002 sec) ``` ```shell obclient [test]> alter table t1 drop index idx1; Query OK, 0 rows affected (0.035 sec) obclient [test]> select * from information_schema.table_constraints where table_name='t1'\G Empty set (0.001 sec) ``` ## 适用版本 OceanBase 数据库 V2.x、V3.x 的各个版本 Previous [OceanBase 数据库 Oracle 模式下如何查看外键约束关系](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000284432) Next [MySQL 租户 foreign key 不支持非唯一键](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207714) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线