主键约束、唯一约束和唯一索引的区别

1. 主键(PRIMARY)

  • 主键用于唯一地标识表中的每一条记录,可以定义一列或多列为主键,一个表上仅只能有一个主键;
  • 不建议更新主键;
  • 主键列上没有任何两行具有相同值(即重复值),且不允许空(NULL)
  • 主健可作外健,唯一索引不可;

2. 唯一约束(UNIQUE)

  • 唯一约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问某行的可选手段,一个表上可以放置多个唯一性约束;
  • 只要唯一就可以更新;
  • 表中任意两行在指定列上都不允许有相同的值,但允许空(NULL)

3. 唯一索引(UNIQUE INDEX)

  • 唯一索引可以确保任何生成重复键值的尝试都会失败;
  • 只要唯一就可以更新;
  • 表中任意两行在指定列上都不允许有相同的值,但允许空(NULL)

4. 唯一性约束和主键约束的区别:

  • 唯一性约束允许在该列上存在NULL值;
  • 主键约束的限制更为严格,不但不允许有重复,而且也不允许有空值;

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

  • 前者是用来检查数据的正确性,后者用来实现数据查询的优化,目的不同。 唯一性约束与唯一索引有所不同:
  • 创建唯一约束会在SinoDB中创建一个constraint,同时也会创建一个该约束对应的唯一索引;
  • 创建唯一索引只会创建一个唯一索引,不会创建constraint; 也就是说其实唯一约束是通过创建唯一索引来实现的。

6. 测试

① 创建表、导入基础数据

drop table if exists tab1;
create table tab1(col1 int, col2 varchar(20), col3 varchar(20));
insert into tab1 values(1,'test001','test001');
insert into tab1 values(2,null,'test002');
insert into tab1 values(3,'test003',null);

② 在表的三个列上分别创建主键、唯一约束和唯一索引
  主键值唯一且不允许为null,唯一约束值唯一但允许null,唯一索引值唯一但允许null

alter table tab1 add constraint primary key(col1) constraint pk_tab1_col1;
alter table tab1 add constraint unique(col2) constraint uk_tab1_col2;
create unique index ux_tab1_col3 on tab1 (col3);

③ 通过oncheck -pt命令查看表的报告
  查看表的报告,可以看到三个索引(对应主键、唯一约束和唯一索引),可以注意到主键、唯一约束的索引名称并不是指定的约束名称

$ oncheck -pt testdb3:tab1 |grep fragment
                  Index  212_134 fragment partition datadbs1 in DBspace datadbs1
                  Index  212_135 fragment partition datadbs1 in DBspace datadbs1
                  Index ux_tab1_col3 fragment partition datadbs1 in DBspace datadbs1

④ 三者区别测试

  • 主键插入空值测试:
> insert into tab1 values(null,'test004','test004');

  703: Primary key on table (tab1) has a field with a null key value.
Error in line 1
Near character position 48

  报错:703: Primary key on table (tab1) has a field with a null key value. 不允许空值。

  • 主键插入重复值测试:
> insert into tab1 values(3,'test005','test005');

  268: Unique constraint (informix.pk_tab1_col1) violated.

  100: ISAM error:  duplicate value for a record with unique key.
Error in line 1
Near character position 45

  报错: 268: Unique constraint (informix.pk_tab1_col1) violated.违反主键约束(pk_tab1_col1),因已经存在值 3 。

  • 唯一约束插入重复值测试:
> insert into tab1 values(4,null,'test006');

  268: Unique constraint (informix.uk_tab1_col2) violated.

  100: ISAM error:  duplicate value for a record with unique key.
Error in line 1
Near character position 40

  报错:268: Unique constraint (informix.uk_tab1_col2) violated.违反唯一约束(uk_tab1_col2),因已经存在值 null 。

  • 唯一索引插入重复值测试:
> insert into tab1 values(6,'test008',null);

  239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:ux_tab1_col3).

  100: ISAM error:  duplicate value for a record with unique key.
Error in line 1
Near character position 40

  报错:239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:ux_tab1_col3). 不允许插入重复值,因已经存在值 null 。

  • 删除唯一约束测试
> alter table tab1 drop constraint uk_tab1_col2;

Table altered.

  此时通过oncheck -pt命令查看表的报告,发现该约束对应的索引页一并删除了:

$ oncheck -pt testdb3:tab1 |grep fragment
                  Index  212_134 fragment partition datadbs1 in DBspace datadbs1
                  Index ux_tab1_col3 fragment partition datadbs1 in DBspace datadbs1

所以:

  1. 如果字段加上not null约束,再加上唯一约束或者唯一索引,功能上将等同于主键约束;
  2. 唯一约束仅仅比唯一索引多了一个约束;