相关文章推荐
玩手机的啄木鸟  ·  【ThinkPHP】like同一个字段多个条 ...·  2 月前    · 
彷徨的山楂  ·  【PyQt5设计】：自动点击神器 - ...·  5 月前    · 
睿智的领结  ·  UUID.randomUUID()简单介绍_ ...·  1 年前    · 
爱搭讪的大蒜  ·  Keras图像数据特征标准化 - 知乎·  1 年前    · 
深情的单杠  ·  聊聊移动端跨平台开发的各种技术-腾讯云开发者 ...·  1 年前    · 
Code  ›  表的唯一约束的作用 KingbaseES VS Oracle - KINGBASE研究院
mysql创建数据库 mysql创建表 唯一索引 唯一约束
https://www.cnblogs.com/kingbase/p/16743682.html
好帅的楼梯
11 月前

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

什么是唯一约束

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

唯一性约束能够在创建表时或使用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
(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
(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 selected

    Oracle的单列唯一约束，不检查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.
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

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

    SQLserver的唯一约束
    
1> create table t1 (c1 int, c2 int);
1> alter table t1 add constraint t1_u1 unique (c1);
1> insert into t1 values (1, 1);
(1 rows affected)
1> insert into t1 values (1, 2);
Msg 2627, Level 14, State 1, Server buxspc, Line 1
违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (1)。
语句已终止。
1> insert into t1 values (null, 2);
(1 rows affected)
1> insert into t1 values (null, 3);
Msg 2627, Level 14, State 1, Server buxspc, Line 1
违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (<NULL>)。
语句已终止。
1> select * from t1;
c1          c2
----------- -----------
          1           1
       NULL           2
(2 rows affected)

    SQLserver的单列唯一约束，检查null值的数据。
    
1> create table t1 (c1 int, c2 int);
1> alter table t1 add constraint t1_u1 unique (c1, c2);
1> insert into t1 values (1, 1);
(1 rows affected)
1> insert into t1 values (1, 1);
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);
(1 rows affected)
1> insert into t1 values (null, 2);
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);
(1 rows affected)
1> insert into t1 values (null, null);
Msg 2627, Level 14, State 1, Server buxspc, Line 1
违反了 UNIQUE KEY 约束“t1_u1”。不能在对象“dbo.t1”中插入重复键。重复键值为 (<NULL>, <NULL>)。
语句已终止。
1> select * from t1 ;
c1          c2
----------- -----------
       NULL        NULL
       NULL           2
          1           1
(3 rows affected)

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

    使用唯一索引建立约束
    

    1> create table t1 (c1 int, c2 int);
1> create unique index t1_c1 on t1 (c1);
1> SELECT name FROM sys.indexes where object_id = (select object_id  from sys.all_objects where name = 't1' ) and index_id >0;
--------------------------------------------------------------------------------------------------------------------------------
t1_c1
(1 rows affected)
1> alter table t1 add constraint t1_U1 unique (c1);
1> SELECT name FROM sys.indexes where object_id = (select object_id  from sys.all_objects where name = 't1' ) and index_id >0;
--------------------------------------------------------------------------------------------------------------------------------
t1_c1
t1_U1
(2 rows affected)
1> alter table t1 drop constraint t1_U1;
1> SELECT name FROM sys.indexes where object_id = (select object_id  from sys.all_objects where name = 't1' ) and index_id >0;
--------------------------------------------------------------------------------------------------------------------------------
t1_c1
(1 rows affected)

    已存在唯一索引，不能被用于唯一约束，唯一约束会创建自用的唯一索引。
    
1> create table t1 (c1 int, c2 int);
1> alter table t1 add constraint t1_u1 unique (c1);
1> create unique index t1_c2 on t1 (c2);
1> create table t2 (c1 int, c2 int);
1> alter table t2 add constraint fk_c1 foreign key(c1) references t1(c1);
1> alter table t2 add constraint fk_c2 foreign key(c2) references t1(c2);
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');
foreign_key_name          table_name
------------------------- ------------------
fk_c1                     t2
fk_c2                     t2
(2 rows affected)

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

    null值处理
    

  • KingbaseES、 MySQL ：只要约束列中包含null值，则不进行约束检查。如果某个约束列是null值，则不能保证记录的唯一。
    • 

  • Oracle ： 全部约束列都是null值，则不进行约束检查。如果全部约束列是null值，则不能保证记录的唯一。
    • 

  • SqlServer ： 约束列的null值，也精细约束检查。null值也被视为有效值，保证记录的唯一。
    • 

    已存在唯一索引处理
    

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

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

    外键关联处理
    

  • KingbaseES、Oracle  ：只能参考唯一约束，不能参考唯一索引。
    • 

  • MySQL 、SqlServer ：既可以参考唯一约束，也可以参考唯一索引。****
    •
     
    推荐文章
    玩手机的啄木鸟  ·  【ThinkPHP】like同一个字段多个条件查询-阿里云开发者社区
    2 月前
    彷徨的山楂  ·  【PyQt5设计】：自动点击神器 - 解决重复性的点击和输入操作_python qt5 按钮点击-CSDN博客
    5 月前
    睿智的领结  ·  UUID.randomUUID()简单介绍_御前提笔小书童的博客-CSDN博客
    1 年前
    爱搭讪的大蒜  ·  Keras图像数据特征标准化 - 知乎
    1 年前
    深情的单杠  ·  聊聊移动端跨平台开发的各种技术-腾讯云开发者社区-腾讯云
    1 年前
    今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
    删除内容请联系邮箱 2879853325@qq.com
    Code - 代码工具平台
    © 2024 ~ 沪ICP备11025650号