暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 人大金仓KingbaseES表的唯一约束的作用

人大金仓KingbaseES表的唯一约束的作用

数据猿 2023-01-17
1296


概述

演示唯一约束怎样创建、删除、禁用和使用唯一性约束，已经多种数据库的差异。

什么是唯一约束

唯一性约束指表中一个字段或者多个字段联合起来可以唯一标识一条记录的约束， 字段中，可以包括空值。

唯一性约束能够在创建表时或使用ALTER TABLE语句创建。

唯一约束与唯一索引的区别

  • 唯一约束和唯一索引，都可以实现列数据的唯一，列值可以有null。
  • 创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。
  • 创建一个唯一索引，这个索引就是独立，可以单独删除。
  • 如果表的一个字段，要作为另外一个表的外键，这个字段必须有唯一约束（或是主键），如果只是有唯一索引，就会报错。

唯一约束和主键的区别

  • 主键（Primary Key）：全部组成主键的列都不能包括空值。
  • 唯一性约束（Unique Constraint）：假设唯一性约束由多列组成，当中的部分列能够包括空值。
  • 主键和唯一性约束自动建立了同名唯一性索引。
  • 一个表只能有一个主键，但可以有多个约束。

KingbaseES的唯一约束

  1. 单列约束
kingbase=# createtable t1 (c1 int, c2 int);
CREATETABLE
kingbase=# altertable t1 addconstraint t1_u1 unique (c1);
ALTERTABLE
kingbase=# insertinto t1 values (1, 1);
INSERT01
kingbase=# insertinto t1 values (1, 2);
错误:  重复键违反唯一约束"t1_u1"
描述:  键值"(c1)=(1)" 已经存在
kingbase=# insertinto t1 values (null, 2);
INSERT01
kingbase=# insertinto t1 values (null, 3);
INSERT01
kingbase=# select*from t1;
 c1 | c2
----+----1|1|2|3
(3 行记录)

KingbaseES的单列唯一约束，不检查null值的数据。

  1. 多列约束
kingbase=# createtable t1 (c1 int, c2 int);
CREATETABLE
kingbase=# altertable t1 addconstraint t1_u1 unique (c1, c2);
ALTERTABLE
kingbase=# insertinto t1 values (1, 1);
INSERT01
kingbase=# insertinto t1 values (1, 1);
错误:  重复键违反唯一约束"t1_u1"
描述:  键值"(c1, c2)=(1, 1)" 已经存在
kingbase=# insertinto t1 values (null, 2);
INSERT01
kingbase=# insertinto t1 values (null, 2);
INSERT01
kingbase=# insertinto t1 values (3, null);
INSERT01
kingbase=# insertinto t1 values (3, null);
INSERT01
kingbase=# insertinto t1 values (null, null);
INSERT01
kingbase=# insertinto t1 values (null, null);
INSERT01
kingbase=# select*from t1 ;
 c1 | c2
----+----1|1|2|23|3|||
(7 行记录)

KingbaseES的多列唯一约束，不检查含有null值的数据。如果某个约束列是null值，则不能保证记录的唯一。

  1. 使用唯一索引建立约束
kingbase=# createtable t1 (c1 int, c2 int);
CREATETABLE
kingbase=# createunique 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=# altertable t1 addconstraint t1_u1 uniqueusing index t1_c1;
注意:  ALTERTABLE/ADDCONSTRAINTUSING INDEX 会把索引 "t1_c1" 重命名为 "t1_u1"
ALTERTABLE
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=# altertable t1 dropconstraint t1_u1;
ALTERTABLE
kingbase=# select indexrelid,indexrelid::regclass::text from pg_index where indrelid='t1'::regclass;
 indexrelid | indexrelid
------------+------------
(0 行记录)

已存在唯一索引，被用于唯一约束之后，索引名会被修改为约束名。唯一索引同时不能单独被删除，删除唯一约束的同时，删除其使用的索引。

  1. 外键

kingbase=# createtable t1 (c1 int, c2 int);
CREATETABLE
kingbase=# altertable t1 addconstraint t1_u1 unique (c1);
ALTERTABLE
kingbase=# createunique index t1_c2 on t1 (c2);
CREATE INDEX
kingbase=# createtable t2 (c1 int, c2 int);
CREATETABLE
kingbase=# altertable t2 addconstraint fk_c1 foreign key(c1) references t1(c1);
ALTERTABLE
kingbase=# altertable t2 addconstraint fk_c2 foreign key(c2) references t1(c2);
错误:  there isnouniqueconstraint matching given keys for referenced table "t1"
kingbase=#

外键，只能参考唯一约束，不能参考唯一索引。

MySQL的唯一约束

  1. 单列约束

mysql>createtable t1 (c1 int, c2 int);
Query OK, 0rows affected (0.01 sec)

mysql>altertable t1 addconstraint t1_u1 unique (c1);
Query OK, 0rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>insertinto t1 values (1, 1);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (1, 2);
ERROR 1062 (23000): Duplicate entry '1'for key 't1.t1_u1'
mysql>insertinto t1 values (null, 2);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (null, 3);
Query OK, 1row affected (0.00 sec)

mysql>select*from t1;
+------+------+
| c1   | c2   |
+------+------+
|  1   |  1   |
| NULL |  2   |
| NULL |  3   |
+------+------+
3rowsinset 
(0.00 sec)

mysql>

MySQL的单列唯一约束，不检查null值的数据。

  1. 多列约束

mysql>createtable t1 (c1 int, c2 int);
Query OK, 0rows affected (0.01 sec)

mysql>altertable t1 addconstraint t1_u1 unique (c1);
Query OK, 0rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>insertinto t1 values (1, 1);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (1, 2);
ERROR 1062 (23000): Duplicate entry '1'for key 't1.t1_u1'
mysql>insertinto t1 values (null, 2);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (null, 3);
Query OK, 1row affected (0.00 sec)

mysql>select*from t1;
+------+------+
| c1   | c2   |
+------+------+
|  1   |  1   |
| NULL |  2   |
| NULL |  3   |
+------+------+
3rowsinset
 (0.00 sec)

mysql>droptable t1;
Query OK, 0rows affected (0.01 sec)

mysql>createtable t1 (c1 int, c2 int);
Query OK, 0rows affected (0.01 sec)

mysql>altertable t1 addconstraint t1_u1 unique (c1, c2);
Query OK, 0rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>insertinto t1 values (1, 1);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (1, 1);
ERROR 1062 (23000): Duplicate entry '1-1'for key 't1.t1_u1'
mysql>insertinto t1 values (null, 2);
Query OK, 1row affected (0.01 sec)

mysql>insertinto t1 values (null, 2);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (3, null);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (3, null);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (null, null);
Query OK, 1row affected (0.00 sec)

mysql>insertinto t1 values (null, null);
Query OK, 1row affected (0.00 sec)

mysql>select*from t1 ;
+------+------+
| c1   | c2   |
+------+------+
| NULL | NULL |
| NULL | NULL |
| NULL |  2   |
| NULL |  2   |
|  1   |  1   |
|  3   | NULL |
|  3   | NULL |
+------+------+
7rowsinset
 (0.00 sec)

mysql>

MySQL的多列唯一约束，不检查含有null值的数据。如果某个约束列是null值，则不能保证记录的唯一。

  1. 使用唯一索引建立约束

mysql>createtable t1 (c1 int, c2 int);
Query OK, 0rows affected (0.01 sec)

mysql>createunique index t1_c1 on t1 (c1);
Query OK, 0rows 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|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1rowinset
 (0.00 sec)

mysql>altertable t1 addconstraint t1_u1 unique (c1);
Query OK, 0rows 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|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2rowsinset 
(0.01 sec)

mysql>altertable t1 dropconstraint t1_u1;
Query OK, 0rows 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|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+1rowinset (0.01 sec)

mysql>

已存在唯一索引，不会用于唯一约束，唯一约束会创建自用的唯一索引。

  1. 外键

mysql>createtable t1 (c1 int, c2 int);
Query OK, 0rows affected (0.01 sec)

mysql>altertable t1 addconstraint t1_u1 unique (c1);
Query OK, 0rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>createunique index t1_c2 on t1 (c2);
Query OK, 0rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>createtable t2 (c1 int, c2 int);
Query OK, 0rows affected (0.01 sec)

mysql>altertable t2 addconstraint fk_c1 foreign key(c1) references t1(c1);
Query OK, 0rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>altertable t2 addconstraint fk_c2 foreign key(c2) references t1(c2);
Query OK, 0rows 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     |
+-----------------+-----------------+
2rowsinset
 (0.00 sec)

mysql>

外键，既可以参考唯一约束，也可以参考唯一索引。

Oracle的唯一约束

  1. 单列约束
SQL>createtable t1 (c1 int, c2 int);

Table created.

SQL>altertable t1 addconstraint t1_u1 unique (c1);

Table altered.

SQL>select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1';

INDEX_NAME
--------------------------------------------------------------------------------
T1_U1

SQL>insertinto t1 values (1, 1);

1row created.

SQL>insertinto t1 values (1, 2);
insertinto t1 values (1, 1)
*
ERROR at line 1:
ORA-00001: uniqueconstraint (SCH01.T1_U1) violated


SQL>insertinto t1 values (null, 2);

1row created.

SQL>insertinto t1 values (null, 3);

1row created.

SQL>select*from t1;

        C1         C2
---------- ----------
1           1
NULL        2
NULL        3
3rows selected.

SQL>altertable t1 dropconstraint t1_u1;

Table altered.

SQL>select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1';

norows selected

Oracle的单列唯一约束，不检查null值的数据。

  1. 多列约束
SQL>createtable t1 (c1 int, c2 int);

Table created.

SQL>altertable t1 addconstraint t1_u1 unique (c1, c2);

Table altered.

SQL>insertinto t1 values (1, 1);

1row created.

SQL>insertinto t1 values (1, 1);
insertinto t1 values (1, 1)
*
ERROR at line 1:
ORA-00001: uniqueconstraint (SCH01.T1_U1) violated

SQL>insertinto t1 values (null, 2);

1row created.

SQL>insertinto t1 values (null, 2);
insertinto t1 values (null, 2)
*
ERROR at line 1:
ORA-00001: uniqueconstraint (SCH01.T1_U1) violated

SQL>insertinto t1 values (3, null);

1row created.

SQL>insertinto t1 values (3, null);
insertinto t1 values (3, null)
*
ERROR at line 1:
ORA-00001: uniqueconstraint (SCH01.T1_U1) violated

SQL>insertinto t1 values (null, null);

1row created.

SQL>insertinto t1 values (null, null);

1row created.

SQL>select*from t1 ;

        C1         C2
---------- ----------
1       1
NULL    2
3      NULL
NULL   NULL
NULL   NULL
5rows selected.

Oracle的多列唯一约束，不检查约束列都是null值的数据。如果全部约束列是null值，则不能保证记录的唯一。

  1. 使用唯一索引建立约束
SQL>createtable t1 (c1 int, c2 int);

Table created.

SQL>createunique 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>altertable t1 addconstraint 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 ofunique/primary key

SQL>altertable t1 dropconstraint t1_u1;

Table altered.

SQL>select INDEX_NAME from SYS.USER_INDEXES where TABLE_NAME='T1';

INDEX_NAME
--------------------------------------------------------------------------------
T1_C1

已存在唯一索引，被用于唯一约束之后，索引名不会改变。同时不能单独被删除。唯一索引同时不能单独被删除，删除唯一约束的同时，不会删除其使用的索引。

  1. 外键
SQL>createtable t1 (c1 int, c2 int);

Table created.

SQL>altertable t1 addconstraint t1_u1 unique (c1);

Table altered.

SQL>createunique index t1_c2 on t1 (c2);

Index created.

SQL>createtable t2 (c1 int, c2 int);

Table created.

SQL>altertable t2 addconstraint fk_c1 foreign key(c1) references t1(c1);

Table altered.

SQL>altertable t2 addconstraint fk_c2 foreign key(c2) references t1(c2);
altertable t2 addconstraint fk_c2 foreign key(c2) references t1(c2)
                                                                  *
ERROR at line 1:
ORA-02270: no matching uniqueorprimary key for this column-list


SQL>

外键，只能参考唯一约束，不能参考唯一索引。

SQLserver的唯一约束

  1. 单列约束
1>createtable t1 (c1 int, c2 int);
2> go
1>altertable t1 addconstraint t1_u1 unique (c1);
2> go
1>insertinto t1 values (1, 1);
2> go

(1rows affected)
1>insertinto t1 values (1, 2);
2> go
Msg 2627, Level 14, State 1, Server buxspc, Line 1
违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (1)。
语句已终止。
1>insertinto t1 values (null, 2);
2> go

(1rows affected)
1>insertinto 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

(2rows affected)
1>

SQLserver的单列唯一约束，检查null值的数据。

  1. 多列约束
1>createtable t1 (c1 int, c2 int);
2> go
1>altertable t1 addconstraint t1_u1 unique (c1, c2);
2> go
1>insertinto t1 values (1, 1);
2> go

(1rows affected)
1>insertinto 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>insertinto t1 values (null, 2);
2> go

(1rows affected)
1>insertinto 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>insertinto t1 values (null, null);
2> go

(1rows affected)
1>insertinto 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

(3rows affected)
1>

SQLserver的多列唯一约束，检查含有null值的数据。把null值当做有效值，保证记录的唯一。

  1. 使用唯一索引建立约束
1>createtable t1 (c1 int, c2 int);
2> go
1>createunique 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

(1rows affected)
1>altertable t1 addconstraint 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

(2rows affected)
1>altertable t1 dropconstraint 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

(1rows affected)
1>

已存在唯一索引，不能被用于唯一约束，唯一约束会创建自用的唯一索引。

  1. 外键
1>createtable t1 (c1 int, c2 int);
2> go
1>altertable t1 addconstraint t1_u1 unique (c1);
2> go
1>createunique index t1_c2 on t1 (c2);
2> go
1>createtable t2 (c1 int, c2 int);
2> go
1>altertable t2 addconstraint fk_c1 foreign key(c1) references t1(c1);
2> go
1>altertable t2 addconstraint 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

(2rows affected)
1>

外键，既可以参考唯一约束，也可以参考唯一索引。

总结

null值处理

  • KingbaseES、 MySQL ：只要约束列中包含null值，则不进行约束检查。如果某个约束列是null值，则不能保证记录的唯一。
  • Oracle ： 全部约束列都是null值，则不进行约束检查。如果全部约束列是null值，则不能保证记录的唯一。
  • SqlServer ： 约束列的null值，也精细约束检查。null值也被视为有效值，保证记录的唯一。

已存在唯一索引处理

  • KingbaseES、Oracle ：唯一约束可以关联到已存在的索引。
  • MySQL 、SqlServer ：唯一约束不能关联到已存在的索引。

外键关联处理

  • KingbaseES、Oracle ：只能参考唯一约束，不能参考唯一索引。
  • MySQL 、SqlServer ：既可以参考唯一约束，也可以参考唯一索引。
人大金仓金仓数据库kingbase
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论