谈谈唯一约束和唯一索引

 

 

约束 全称完整性约束，它是关系数据库中的对象，用来存放插入到一个表中一列数据的规则，用来确保数据的准确性和一致性。

索引 数据库中用的最频繁的操作是数据查询，索引就是为了加速表中数据行的检索而创建的一种分散的数据结构。可以把索引类比成书的目录，有目录的肯定比没有目录的书，更方便查找。

唯一约束 保证在一个字段或者一组字段里的数据都与表中其它行的对应数据不同。和主键约束不同，唯一约束允许为 NULL，只是只能有一行。

唯一索引 不允许具有索引值相同的行，从而禁止重复的索引或键值。

唯一约束和唯一索引 都是为了保证表中被限制的字段不允许有重复的值，看起来功能是一样的，那为什么要设计这样两种一样的功能呢？

 

 

首先创建两个字段值一样的表 t1，t2，并为 t1 表中的 col1 列设置唯一约束。

 

CREATE TABLE t1 (
    col1 INT(11), 
    col2 VARCHAR(20), 
    CONSTRAINT t1_uq UNIQUE (col1)
);

CREATE TABLE t2 (
    col1 INT(11), 
    col2 VARCHAR(20)
);
 

 

然后为表 t2 表中的 col1 列设置唯一索引

CREATE UNIQUE INDEX 
    t2_idx ON t2 (col1);

　　

创建表 t3，并将 t1 表中的 col1 列设置为 t3 表中 col2 列的外键

CREATE TABLE t3 (
    col1 INT(11), 
    col2 INT(11), 
    col3 VARCHAR(20), 
    CONSTRAINT t3_fk FOREIGN KEY (col2) REFERENCES t1 (col1)
);
 

 

 

到此为止，基本上就能得出，唯一约束和唯一索引在 MySQL 数据库里区别了

1. 概念上不同，约束是为了保证数据的完整性，索引是为了辅助查询；
2. 创建唯一约束时，会自动的创建唯一索引；
3. 在理论上，不一样，在实际使用时，基本没有区别。

关于第二条，MySQL 中唯一约束是通过唯一索引实现的，为了保证没有重复值，在插入新记录时会再检索一遍，怎样检索快，当然是建索引了，所以，在创建唯一约束的时候就创建了唯一索引。 

 

 

4.唯一性索引

如果确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候，就应该用关键字UNIQUE把它定义为一个唯一索引，Mysql会在有新纪录插入数据表时，自动检查新纪录的这个字段的值是否已经在某个记录的这个字段里出现过了。如果是，mysql将拒绝插入那条新纪录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复

5.索引的优点

5.1.可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性

5.2.建立索引可以大大提高检索的数据，以及减少表的检索行数

5.3.在表连接的连接条件，可以加速表与表直接的相连

5.4.在分组和排序字句进行数据检索，可以减少查询时间中分组和排序时所消耗的时间（数据库的记录会重新排序）

5.5.建立索引，在查询中使用索引，可以提高性能。

 

CREATE TABLE parent(
 2 
 3                     id INT NOT NULL,
 4 
 5                     PRIMARY KEY (id)
 6 
 7 ) TYPE=INNODB;
 8 
 9  CREATE TABLE child(
10 
11                    id INT, parent_id INT,
12 
13                    INDEX par_ind (parent_id),
14 
15                    FOREIGN KEY (parent_id) REFERENCES parent(id)
16 
17                    ON DELETE CASCADE
18 
19 ) TYPE=INNODB;

      如下是一个更复杂的例子，其中一个product_order表对其它两个表有外键。一个外键引用一个product表中的双列索引。另一个引用在customer表中的单行索引：

 1 CREATE TABLE product (
2
3 category INT NOT NULL,
4
5 id INT NOT NULL,
6
7 price DECIMAL,
8
9 PRIMARY KEY(category, id)
10
11 ) TYPE=INNODB;
12
13  CREATE TABLE customer (id INT NOT NULL,
14
15 PRIMARY KEY (id)
16
17 ) TYPE=INNODB;
18

 

 

19  CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
20
21 product_category INT NOT NULL,
22
23 product_id INT NOT NULL,
24
25 customer_id INT NOT NULL,
26
27 PRIMARY KEY(no),
28
29 INDEX (product_category, product_id),
30
31 FOREIGN KEY (product_category, product_id)
32
33 REFERENCES product(category, id)
34
35 ON UPDATE CASCADE ON DELETE RESTRICT,
36
37 INDEX (customer_id),
38
39 FOREIGN KEY (customer_id)
40
41 REFERENCES customer(id)
42
43 ) TYPE=INNODB;