PG 唯一约束与唯一索引
实例create table test(id serial, name text);ALTER TABLE test add CONSTtraint uk_test_name unique(name);create table test2(id serial, name text);create unique index idx_test2_name on test2 using btree(name);insert into test (name) values('zhangsan'),(NULL),(NULL);insert into test2 (name) values('zhangsan'),(NULL),(NULL);
结果
test=# \d testTable "public.test"Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+----------------------------------id| integer | | not null | nextval('test_id_seq'::regclass)name | text | || Indexes:"uk_test_name" UNIQUE CONSTRAINT, btree (name)test=# \d test2Table "public.test2"Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+-----------------------------------id| integer | | not null | nextval('test2_id_seq'::regclass)name | text | || Indexes:"idx_test2_name" UNIQUE, btree (name)test=# select * from test;id | name ----+----------1 | zhangsan2 | 3 | (3 rows)test=# select * from test2;id | name ----+----------1 | zhangsan2 | 3 | (3 rows)test=#
限制唯一索引的多个 NULL 值
实例create table t_abc(a2 varchar(10));CREATE UNIQUE INDEX idx_t_abc ON t_abc ((a2 is null)) WHERE a2 IS NULL;insert into t_abc values('1');insert into t_abc values(NULL);insert into t_abc values(NULL);
测试结果
test=# create table t_abc(a2 varchar(10));CREATE TABLEtest=# test=# CREATE UNIQUE INDEX idx_t_abc ON t_abc ((a2 is null)) WHERE a2 IS NULL;CREATE INDEXtest=# test=# insert into t_abc values('1');INSERT 0 1test=# insert into t_abc values(NULL);INSERT 0 1test=# insert into t_abc values(NULL);ERROR: duplicate key value violates unique constraint "idx_t_abc"DETAIL: Key ((a2 IS NULL))=(t) already exists.test=#
总结：
唯一索引与唯一约束在 PG 中功能几乎相同唯一索引与唯一约束不能直接限制多个NULL 值在创建唯一索引的时候可以使用额外的参数,因而可以间接实现限制 多个 NULL 值的情况
