OceanBase 数据库中唯一性约束与普通约束的区别

本文主要介绍 OceanBase 数据库中唯一性约束和其它几类普通约束（NOT NULL 约束、外键约束、Check 约束）的区别。

OceanBase 数据库支持多种类型的完整性约束。

用户可以使用以下完整性约束对输入的列值加以限制：

• NOT NULL 完整性约束
• UNIQUE KEY 完整性约束
• PRIMARY KEY 完整性约束
• 引用完整性约束

这边看上去 UNIQUE 唯一性约束跟其它几类普通约束（NOT NULL 约束、外键约束、Check 约束）并无明显的区别，但是由于在 OceanBase 数据库中 UNIQUE 约束是通过索引来实现的，唯一性约束本质上是一个唯一性索引，因此，OceanBase 数据库的 UNIQUE 唯一性约束和其它几类普通约束有以下几个重大的区别。

1. 添加唯一性约束时，不支持指定 enable/validate/rely 等状态。

   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
   

   而其它类型的约束都是支持的。

   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 版本行为保持一致）。

   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 唯一性约束。

   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. 添加一个唯一性约束后，会自动添加一个同名的索引；反之，添加一个唯一索引，也会自动添加一个同名的唯一性约束。

   obclient [SYS]> alter table t1 add constraint const1 unique(name);
   Query OK, 0 rows affected (0.544 sec)
   

   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
   

   obclient [SYS]> alter table t1 drop constraint const1;
   Query OK, 0 rows affected (0.035 sec)
   

   也可以直接 drop 对应的唯一索引。

   obclient [SYS]> drop index CONST1;
   Query OK, 0 rows affected (0.029 sec)
   

   obclient [SYS]> create unique index idx1 on t1(name);
   Query OK, 0 rows affected (0.539 sec)
   

   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
   

   obclient [SYS]> drop index idx1;
   Query OK, 0 rows affected (0.028 sec)
   

   也可以直接 drop 对应的唯一约束。

   obclient [SYS]> alter table t1 drop constraint idx1;
   Query OK, 0 rows affected (0.034 sec)
   

   obclient [test]> alter table t1 add constraint const1 unique(name);
   Query OK, 0 rows affected (4.411 sec)
   

   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)
   

   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)
   

   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)
   

   obclient [test]> create unique index idx1 on t1(name);
   Query OK, 0 rows affected (5.158 sec)
   

   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)
   

   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)
   

   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 的各个版本