Hubble数据库唯一索引

• Hubble数据库中 UNIQUE 约束指定约束列中的每个非 null 值必须是唯一的。

数据准备

create table unique_index (id int PRIMARY KEY ,name string,tel string,sex string ,unique(name));

insert into unique_index values(1,'杨洋','1111','男'),(2,'刘亦菲','2222','女');

hubble@hadoop1:35436/defaultdb> create table unique_index (id int PRIMARY KEY ,name string,tel string,sex string ,unique(name));
CREATE TABLE


Time: 50ms total (execution 49ms / network 1ms)

hubble@hadoop1:35436/defaultdb> 
hubble@hadoop1:35436/defaultdb> 
hubble@hadoop1:35436/defaultdb> insert into unique_index values(1,'杨洋','1111','男'),(2,'刘亦菲','2222','女');
INSERT 2


Time: 90ms total (execution 89ms / network 0ms)

hubble@hadoop1:35436/defaultdb>

唯一索引插入重复数据

Hubble 数据库中唯一索引字段插入重复数据会报错

hubble@hadoop1:35436/defaultdb> insert into unique_index values(3,'杨洋','1111','男'),(4,'刘亦菲','2222','女');
ERROR: duplicate key value violates unique constraint "unique_index_name_key"
SQLSTATE: 23505
DETAIL: Key (name)=(e'\U00006768\U00006D0B') already exists.
CONSTRAINT: unique_index_name_key
hubble@hadoop1:35436/defaultdb>

查看唯一索引执行计划

explain select * from unique_index where name='杨洋';

hubble@hadoop1:35436/defaultdb> explain select * from unique_index where name='杨洋';
                                                               info
----------------------------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ estimated row count: 1
  │ table: unique_index@unique_index_pkey
  │
  └── • scan
        estimated row count: 1 (100% of the table; stats collected 2 hours ago)
        table: unique_index@unique_index_name_key
        spans: [/e'\U00006768\U00006D0B' - /e'\U00006768\U00006D0B']

  index recommendations: 1
  1. type: index replacement
     SQL commands: CREATE UNIQUE INDEX ON unique_index (name) STORING (tel, sex); DROP INDEX unique_index@unique_index_name_key;
(15 rows)


Time: 3ms total (execution 2ms / network 1ms)

hubble@hadoop1:35436/defaultdb>