表的唯一约束的作用 KingbaseES VS Oracle
背景
演示唯一约束怎样创建、删除、禁用和使用唯一性约束，已经多种数据库的差异。
什么是唯一约束
唯一性约束指表中一个字段或者多个字段联合起来可以唯一标识一条记录的约束， 字段中，可以包括空值。
唯一性约束能够在创建表时或使用ALTER TABLE语句创建。
唯一约束与唯一索引的区别
- 唯一约束和唯一索引，都可以实现列数据的唯一，列值可以有null。
- 创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。
- 创建一个唯一索引，这个索引就是独立，可以单独删除。
- 如果表的一个字段，要作为另外一个表的外键，这个字段必须有唯一约束（或是主键），如果只是有唯一索引，就会报错。
唯一约束和主键的区别
- 主键（Primary Key）：全部组成主键的列都不能包括空值。
- 唯一性约束（Unique Constraint）：假设唯一性约束由多列组成，当中的部分列能够包括空值。
- 主键和唯一性约束自动建立了同名唯一性索引。
- 一个表只能有一个主键，但可以有多个约束。
KingbaseES的唯一约束
-
单列约束
kingbase=# create table t1 (c1 int, c2 int); CREATE TABLE kingbase=# alter table t1 add constraint t1_u1 unique (c1); ALTER TABLE kingbase=# insert into t1 values (1, 1); INSERT 0 1 kingbase=# insert into t1 values (1, 2); 错误: 重复键违反唯一约束"t1_u1" 描述: 键值"(c1)=(1)" 已经存在 kingbase=# insert into t1 values (null, 2); INSERT 0 1 kingbase=# insert into t1 values (null, 3); INSERT 0 1 kingbase=# select * from t1; c1 | c2 ----+---- 1 | 1 | 2 | 3 (3 行记录)KingbaseES的单列唯一约束，不检查null值的数据。
-
多列约束
kingbase=# create table t1 (c1 int, c2 int); CREATE TABLE kingbase=# alter table t1 add constraint t1_u1 unique (c1, c2); ALTER TABLE kingbase=# insert into t1 values (1, 1); INSERT 0 1 kingbase=# insert into t1 values (1, 1); 错误: 重复键违反唯一约束"t1_u1" 描述: 键值"(c1, c2)=(1, 1)" 已经存在 kingbase=# insert into t1 values (null, 2); INSERT 0 1 kingbase=# insert into t1 values (null, 2); INSERT 0 1 kingbase=# insert into t1 values (3, null); INSERT 0 1 kingbase=# insert into t1 values (3, null); INSERT 0 1 kingbase=# insert into t1 values (null, null); INSERT 0 1 kingbase=# insert into t1 values (null, null); INSERT 0 1 kingbase=# select * from t1 ; c1 | c2 ----+---- 1 | 1 | 2 | 2 3 | 3 | | | (7 行记录)KingbaseES的多列唯一约束，不检查含有null值的数据。如果某个约束列是null值，则不能保证记录的唯一。
-
使用唯一索引建立约束
kingbase=# create table t1 (c1 int, c2 int); CREATE TABLE kingbase=# create unique index t1_c1 on t1 (c1); CREATE INDEX kingbase=# select indexrelid,indexrelid::regclass::text from pg_index where indrelid='t1'::regclass; indexrelid | indexrelid ------------+------------ 212572 | t1_c1 (1 行记录) kingbase=# alter table t1 add constraint t1_u1 unique using index t1_c1; 注意: ALTER TABLE / ADD CONSTRAINT USING INDEX 会把索引 "t1_c1" 重命名为 "t1_u1" ALTER TABLE kingbase=# select indexrelid,indexrelid::regclass::text from pg_index where indrelid='t1'::regclass; indexrelid | indexrelid ------------+------------ 212572 | t1_u1 (1 行记录) kingbase=# drop index t1_c1; 错误: 索引 "t1_c1" 不存在 kingbase=# drop index t1_u1; 错误: 无法删除 索引 t1_u1, 因为 在表 t1上的约束t1_u1 需要它 提示: 您也可以删除 在表 t1上的约束t1_u1 代替. kingbase=# alter table t1 drop constraint t1_u1; ALTER TABLE kingbase=# select indexrelid,indexrelid::regclass::text from pg_index where indrelid='t1'::regclass; indexrelid | indexrelid ------------+------------ (0 行记录)已存在唯一索引，被用于唯一约束之后，索引名会被修改为约束名。唯一索引同时不能单独被删除，删除唯一约束的同时，删除其使用的索引。
-
外键
kingbase=# create table t1 (c1 int, c2 int); CREATE TABLE kingbase=# alter table t1 add constraint t1_u1 unique (c1); ALTER TABLE kingbase=# create unique index t1_c2 on t1 (c2); CREATE INDEX kingbase=# create table t2 (c1 int, c2 int); CREATE TABLE kingbase=# alter table t2 add constraint fk_c1 foreign key(c1) references t1(c1); ALTER TABLE kingbase=# alter table t2 add constraint fk_c2 foreign key(c2) references t1(c2); 错误: there is no unique constraint matching given keys for referenced table "t1" kingbase=#外键，只能参考唯一约束，不能参考唯一索引。
MySQL的唯一约束
-
单列约束
mysql> create table t1 (c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 add constraint t1_u1 unique (c1); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t1 values (1, 1); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (1, 2); ERROR 1062 (23000): Duplicate entry '1' for key 't1.t1_u1' mysql> insert into t1 values (null, 2); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (null, 3); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+------+ | c1 | c2 | +------+------+ | 1 | 1 | | NULL | 2 | | NULL | 3 | +------+------+ 3 rows in set (0.00 sec) mysql>MySQL的单列唯一约束，不检查null值的数据。
-
多列约束
mysql> create table t1 (c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 add constraint t1_u1 unique (c1); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t1 values (1, 1); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (1, 2); ERROR 1062 (23000): Duplicate entry '1' for key 't1.t1_u1' mysql> insert into t1 values (null, 2); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (null, 3); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+------+ | c1 | c2 | +------+------+ | 1 | 1 | | NULL | 2 | | NULL | 3 | +------+------+ 3 rows in set (0.00 sec) mysql> drop table t1; Query OK, 0 rows affected (0.01 sec) mysql> create table t1 (c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 add constraint t1_u1 unique (c1, c2); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t1 values (1, 1); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (1, 1); ERROR 1062 (23000): Duplicate entry '1-1' for key 't1.t1_u1' mysql> insert into t1 values (null, 2); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values (null, 2); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (3, null); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (3, null); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (null, null); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (null, null); Query OK, 1 row affected (0.00 sec) mysql> select * from t1 ; +------+------+ | c1 | c2 | +------+------+ | NULL | NULL | | NULL | NULL | | NULL | 2 | | NULL | 2 | | 1 | 1 | | 3 | NULL | | 3 | NULL | +------+------+ 7 rows in set (0.00 sec) mysql>MySQL的多列唯一约束，不检查含有null值的数据。如果某个约束列是null值，则不能保证记录的唯一。
-
使用唯一索引建立约束
mysql> create table t1 (c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql> create unique index t1_c1 on t1 (c1); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 0 | t1_c1 | 1 | c1 | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) mysql> alter table t1 add constraint t1_u1 unique (c1); Query OK, 0 rows affected, 1 warning (0.01 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 0 | t1_c1 | 1 | c1 | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | | t1 | 0 | t1_u1 | 1 | c1 | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.01 sec) mysql> alter table t1 drop constraint t1_u1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 0 | t1_c1 | 1 | c1 | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.01 sec) mysql>已存在唯一索引，不会用于唯一约束，唯一约束会创建自用的唯一索引。
-
外键
mysql> create table t1 (c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 add constraint t1_u1 unique (c1); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create unique index t1_c2 on t1 (c2); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create table t2 (c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql> alter table t2 add constraint fk_c1 foreign key(c1) references t1(c1); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t2 add constraint fk_c2 foreign key(c2) references t1(c2); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = 't2'; +-----------------+-----------------+ | CONSTRAINT_NAME | CONSTRAINT_TYPE | +-----------------+-----------------+ | fk_c1 | FOREIGN KEY | | fk_c2 | FOREIGN KEY | +-----------------+-----------------+ 2 rows in set (0.00 sec) mysql>外键，既可以参考唯一约束，也可以参考唯一索引。
Oracle的唯一约束
-
单列约束
SQL> create table t1 (c1 int, c2 int); Table created. SQL> alter table t1 add constraint t1_u1 unique (c1); Table altered. SQL> select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1'; INDEX_NAME -------------------------------------------------------------------------------- T1_U1 SQL> insert into t1 values (1, 1); 1 row created. SQL> insert into t1 values (1, 2); insert into t1 values (1, 1) * ERROR at line 1: ORA-00001: unique constraint (SCH01.T1_U1) violated SQL> insert into t1 values (null, 2); 1 row created. SQL> insert into t1 values (null, 3); 1 row created. SQL> select * from t1; C1 C2 ---------- ---------- 1 1 NULL 2 NULL 3 3 rows selected. SQL> alter table t1 drop constraint t1_u1; Table altered. SQL> select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1'; no rows selectedOracle的单列唯一约束，不检查null值的数据。
-
多列约束
SQL> create table t1 (c1 int, c2 int); Table created. SQL> alter table t1 add constraint t1_u1 unique (c1, c2); Table altered. SQL> insert into t1 values (1, 1); 1 row created. SQL> insert into t1 values (1, 1); insert into t1 values (1, 1) * ERROR at line 1: ORA-00001: unique constraint (SCH01.T1_U1) violated SQL> insert into t1 values (null, 2); 1 row created. SQL> insert into t1 values (null, 2); insert into t1 values (null, 2) * ERROR at line 1: ORA-00001: unique constraint (SCH01.T1_U1) violated SQL> insert into t1 values (3, null); 1 row created. SQL> insert into t1 values (3, null); insert into t1 values (3, null) * ERROR at line 1: ORA-00001: unique constraint (SCH01.T1_U1) violated SQL> insert into t1 values (null, null); 1 row created. SQL> insert into t1 values (null, null); 1 row created. SQL> select * from t1 ; C1 C2 ---------- ---------- 1 1 NULL 2 3 NULL NULL NULL NULL NULL 5 rows selected.Oracle的多列唯一约束，不检查约束列都是null值的数据。如果全部约束列是null值，则不能保证记录的唯一。
-
使用唯一索引建立约束
SQL> create table t1 (c1 int, c2 int); Table created. SQL> create unique index t1_c1 on t1 (c1); Index created. SQL> select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1';SQL> INDEX_NAME -------------------------------------------------------------------------------- T1_C1 SQL> select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1'; INDEX_NAME -------------------------------------------------------------------------------- T1_C1 SQL> alter table t1 add constraint t1_u1 unique (c1); Table altered. SQL> select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1'; INDEX_NAME -------------------------------------------------------------------------------- T1_C1 SQL> drop index t1_c1; drop index t1_c1 * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key SQL> alter table t1 drop constraint t1_u1; Table altered. SQL> select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1'; INDEX_NAME -------------------------------------------------------------------------------- T1_C1已存在唯一索引，被用于唯一约束之后，索引名不会改变。同时不能单独被删除。唯一索引同时不能单独被删除，删除唯一约束的同时，不会删除其使用的索引。
-
外键
SQL> create table t1 (c1 int, c2 int); Table created. SQL> alter table t1 add constraint t1_u1 unique (c1); Table altered. SQL> create unique index t1_c2 on t1 (c2); Index created. SQL> create table t2 (c1 int, c2 int); Table created. SQL> alter table t2 add constraint fk_c1 foreign key(c1) references t1(c1); Table altered. SQL> alter table t2 add constraint fk_c2 foreign key(c2) references t1(c2); alter table t2 add constraint fk_c2 foreign key(c2) references t1(c2) * ERROR at line 1: ORA-02270: no matching unique or primary key for this column-list SQL>外键，只能参考唯一约束，不能参考唯一索引。
SQLserver的唯一约束
-
单列约束
1> create table t1 (c1 int, c2 int); 2> go 1> alter table t1 add constraint t1_u1 unique (c1); 2> go 1> insert into t1 values (1, 1); 2> go (1 rows affected) 1> insert into t1 values (1, 2); 2> go Msg 2627, Level 14, State 1, Server buxspc, Line 1 违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (1)。 语句已终止。 1> insert into t1 values (null, 2); 2> go (1 rows affected) 1> insert into t1 values (null, 3); 2> go Msg 2627, Level 14, State 1, Server buxspc, Line 1 违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (<NULL>)。 语句已终止。 1> select * from t1; 2> go c1 c2 ----------- ----------- 1 1 NULL 2 (2 rows affected) 1>SQLserver的单列唯一约束，检查null值的数据。
-
多列约束
1> create table t1 (c1 int, c2 int); 2> go 1> alter table t1 add constraint t1_u1 unique (c1, c2); 2> go 1> insert into t1 values (1, 1); 2> go (1 rows affected) 1> insert into t1 values (1, 1); 2> go Msg 2627, Level 14, State 1, Server buxspc, Line 1 违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (1, 1)。 语句已终止。 1> insert into t1 values (null, 2); 2> go (1 rows affected) 1> insert into t1 values (null, 2); 2> go Msg 2627, Level 14, State 1, Server buxspc, Line 1 违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (<NULL>, 2)。 语句已终止。 1> insert into t1 values (null, null); 2> go (1 rows affected) 1> insert into t1 values (null, null); 2> go Msg 2627, Level 14, State 1, Server buxspc, Line 1 违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (<NULL>, <NULL>)。 语句已终止。 1> select * from t1 ; 2> go c1 c2 ----------- ----------- NULL NULL NULL 2 1 1 (3 rows affected) 1>SQLserver的多列唯一约束，检查含有null值的数据。把null值当做有效值，保证记录的唯一。
-
使用唯一索引建立约束
1> create table t1 (c1 int, c2 int); 2> go 1> create unique index t1_c1 on t1 (c1); 2> go 1> SELECT name FROM sys.indexes where object_id = (select object_id from sys.all_objects where name = 't1' ) and index_id >0; 2> go name -------------------------------------------------------------------------------------------------------------------------------- t1_c1 (1 rows affected) 1> alter table t1 add constraint t1_U1 unique (c1); 2> go 1> SELECT name FROM sys.indexes where object_id = (select object_id from sys.all_objects where name = 't1' ) and index_id >0; 2> go name -------------------------------------------------------------------------------------------------------------------------------- t1_c1 t1_U1 (2 rows affected) 1> alter table t1 drop constraint t1_U1; 2> go 1> SELECT name FROM sys.indexes where object_id = (select object_id from sys.all_objects where name = 't1' ) and index_id >0; 2> go name -------------------------------------------------------------------------------------------------------------------------------- t1_c1 (1 rows affected) 1>已存在唯一索引，不能被用于唯一约束，唯一约束会创建自用的唯一索引。
-
外键
1> create table t1 (c1 int, c2 int); 2> go 1> alter table t1 add constraint t1_u1 unique (c1); 2> go 1> create unique index t1_c2 on t1 (c2); 2> go 1> create table t2 (c1 int, c2 int); 2> go 1> alter table t2 add constraint fk_c1 foreign key(c1) references t1(c1); 2> go 1> alter table t2 add constraint fk_c2 foreign key(c2) references t1(c2); 2> go 1> SELECT f.name AS foreign_key_name ,OBJECT_NAME(f.parent_object_id) AS table_name FROM sys.foreign_keys AS f WHERE f.parent_object_id = OBJECT_ID('t2'); 2> go foreign_key_name table_name ------------------------- ------------------ fk_c1 t2 fk_c2 t2 (2 rows affected) 1>外键，既可以参考唯一约束，也可以参考唯一索引。
总结
null值处理
- KingbaseES、 MySQL ：只要约束列中包含null值，则不进行约束检查。如果某个约束列是null值，则不能保证记录的唯一。
- Oracle ： 全部约束列都是null值，则不进行约束检查。如果全部约束列是null值，则不能保证记录的唯一。
- SqlServer ： 约束列的null值，也精细约束检查。null值也被视为有效值，保证记录的唯一。
已存在唯一索引处理
- KingbaseES、Oracle ：唯一约束可以关联到已存在的索引。
- MySQL 、SqlServer ：唯一约束不能关联到已存在的索引。
外键关联处理
- KingbaseES、Oracle ：只能参考唯一约束，不能参考唯一索引。
- MySQL 、SqlServer ：既可以参考唯一约束，也可以参考唯一索引。****
KINGBASE研究院
