[SQL] 인덱스 대 고유 제한 조건을 POSTGRES

인덱스 대 고유 제한 조건을 POSTGRES

나는이 문서를 이해 수 있듯이 다음과 같은 정의는 동일합니다

create table foo (
    id serial primary key,
    code integer,
    label text,
    constraint foo_uq unique (code, label));

create table foo (
    id serial primary key,
    code integer,
    label text);
create unique index foo_idx on foo using btree (code, label);    

그러나 포스트 그레스 9.4의 사용 설명서에 메모는 말한다 :

(편집 :이 노트는 포스트 그레스 9.5 설명서에서 제거되었습니다.)

그것은 좋은 스타일의 문제인가? 이러한 변형 (예를 들어, 성능)의 선택 하나의 실제적인 결과는 무엇인가?

해결법

1. ==============================

   1.내가 예를 배우게하기로 결정 그래서 나는이 기본적이지만 중요한 문제에 대한 몇 가지 의문을 가지고 있었다.

   내가 예를 배우게하기로 결정 그래서 나는이 기본적이지만 중요한 문제에 대한 몇 가지 의문을 가지고 있었다.

   의 고유 제약 조건 테스트 테이블이 열 마스터, con_id을 만들고 고유 인덱스에 의해 색인 ind_id 보자.

   create table master (
       con_id integer unique,
       ind_id integer
   );
   create unique index master_unique_idx on master (ind_id);
   
       Table "public.master"
    Column |  Type   | Modifiers
   --------+---------+-----------
    con_id | integer |
    ind_id | integer |
   Indexes:
       "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
       "master_unique_idx" UNIQUE, btree (ind_id)
   

   테이블 설명에서 (\ D에서 psql의) 당신은 고유 인덱스에서 고유 제한 조건을 알 수 있습니다.

   고유성

   단지의 경우, 수표의 고유성을 보자.

   test=# insert into master values (0, 0);
   INSERT 0 1
   test=# insert into master values (0, 1);
   ERROR:  duplicate key value violates unique constraint "master_con_id_key"
   DETAIL:  Key (con_id)=(0) already exists.
   test=# insert into master values (1, 0);
   ERROR:  duplicate key value violates unique constraint "master_unique_idx"
   DETAIL:  Key (ind_id)=(0) already exists.
   test=#
   

   그것은 예상대로 작동합니다!

   외부 키

   이제 우리는 두 개의 외래 키는 마스터에 우리 두 컬럼에 참조하여 상세히 테이블을 정의 할 수 있습니다.

   create table detail (
       con_id integer,
       ind_id integer,
       constraint detail_fk1 foreign key (con_id) references master(con_id),
       constraint detail_fk2 foreign key (ind_id) references master(ind_id)
   );
   
       Table "public.detail"
    Column |  Type   | Modifiers
   --------+---------+-----------
    con_id | integer |
    ind_id | integer |
   Foreign-key constraints:
       "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
       "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)
   

   음, 오류없이. 하자의 메이크업은 반드시 그것을 작동합니다.

   test=# insert into detail values (0, 0);
   INSERT 0 1
   test=# insert into detail values (1, 0);
   ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk1"
   DETAIL:  Key (con_id)=(1) is not present in table "master".
   test=# insert into detail values (0, 1);
   ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk2"
   DETAIL:  Key (ind_id)=(1) is not present in table "master".
   test=#
   

   두 열이 외래 키에서 참조 할 수있다.

   제약 사용하여 인덱스

   당신은 기존의 고유 인덱스를 사용하여 테이블 제약 조건을 추가 할 수 있습니다.

   alter table master add constraint master_ind_id_key unique using index master_unique_idx;
   
       Table "public.master"
    Column |  Type   | Modifiers
   --------+---------+-----------
    con_id | integer |
    ind_id | integer |
   Indexes:
       "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
       "master_ind_id_key" UNIQUE CONSTRAINT, btree (ind_id)
   Referenced by:
       TABLE "detail" CONSTRAINT "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
       TABLE "detail" CONSTRAINT "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)
   

   이제 열 제약 기술 차이가 없다.

   부분 인덱스

   테이블 제약 선언에서는 부분 인덱스를 만들 수 없습니다. 그것은 테이블을 생성의 정의에서 직접 온다 .... 고유 인덱스 선언에서는 부분 인덱스를 생성하는 WHERE 절을 설정할 수 있습니다. 또한 발현에 인덱스를 생성 (뿐만 아니라 열에) 및 다른 매개 변수 (정렬, 정렬 순서, NULL을 배치)을 정의 할 수 있습니다.

   당신은 부분 인덱스를 사용하여 테이블에 제약 조건을 추가 할 수 없습니다.

   alter table master add column part_id integer;
   create unique index master_partial_idx on master (part_id) where part_id is not null;
   
   alter table master add constraint master_part_id_key unique using index master_partial_idx;
   ERROR:  "master_partial_idx" is a partial index
   LINE 1: alter table master add constraint master_part_id_key unique ...
                                  ^
   DETAIL:  Cannot create a primary key or unique constraint using such an index.
   

2. ==============================

   2.UNIQUE CONSTRAINT 대 UNIQUE INDEX를 사용하는 또 하나의 장점은 당신이 할 수없는 제약 조건을 가진 반면, 쉽게 / DROP CONCURRENTLY 인덱스를 만들 수 있다는 것입니다.

   UNIQUE CONSTRAINT 대 UNIQUE INDEX를 사용하는 또 하나의 장점은 당신이 할 수없는 제약 조건을 가진 반면, 쉽게 / DROP CONCURRENTLY 인덱스를 만들 수 있다는 것입니다.

3. ==============================

   3.전체 텍스트

   전체 텍스트

   속도 성능은 동일해야합니다 그래서

4. ==============================

   4.내가 만난 또 다른 것은 당신이 고유 인덱스에 있지만 제약의 SQL 표현식을 사용할 수 있다는 것입니다.

   내가 만난 또 다른 것은 당신이 고유 인덱스에 있지만 제약의 SQL 표현식을 사용할 수 있다는 것입니다.

   그래서,이 작동하지 않습니다 :

   CREATE TABLE users (
       name text,
       UNIQUE (lower(name))
   );
   

   하지만 작품을 다음과 같습니다.

   CREATE TABLE users (
       name text
   );
   CREATE UNIQUE INDEX uq_name on users (lower(name));
   

5. ==============================

   5.다양한 사람들이 고유 제한 조건을 통해 고유 인덱스의 장점을 제공하고 있기 때문에, 여기에 단점이다 : 고유 제한 조건은 (단지 트랜잭션의 끝에서 확인) 연기 할 수 있으며, 고유 인덱스는 할 수 없습니다.

   다양한 사람들이 고유 제한 조건을 통해 고유 인덱스의 장점을 제공하고 있기 때문에, 여기에 단점이다 : 고유 제한 조건은 (단지 트랜잭션의 끝에서 확인) 연기 할 수 있으며, 고유 인덱스는 할 수 없습니다.

6. ==============================

   6.나는 문서에이 글을 읽을 :

   나는 문서에이 글을 읽을 :

   그래서 나는 당신이 제약 조건을 추가하여 "부분 고유성"라고 부릅니다 생각합니다.

   그리고, 대한 고유성을 보장하는 방법 :

   우리는 색인을 생성 제약 조건을 추가한다 그래서, 고유성을 보장합니다.

   이 문제를 어떻게보고?

   A "제약"목표는 gramatically이 열이 고유해야 보장하기 위해서는 법률, 규칙을 설정; "인덱스", 의미 론적 대해 동안 "구현하는 방법, 고유성을 달성하는 방법, 독특한 수단을 무엇 그것을 구현에 올 때". 그래서, PostgreSQL을, 그것은을 구현하는 방법은 매우 논리적이다 : 첫째, 당신은 열이 고유해야 함을 선언 한 후, PostgreSQL는 당신을 위해 고유 인덱스를 추가 구현을 추가합니다.

7. ==============================

   7.잠금에 차이가 있습니다. 인덱스를 추가하면 테이블에 대한 읽기 액세스를 차단하지 않습니다. 제약 조건을 추가하면 (모든 선택이 차단되도록)이 ALTER TABLE을 통해 추가되기 때문에 테이블 잠금을 넣어 않습니다.

   잠금에 차이가 있습니다. 인덱스를 추가하면 테이블에 대한 읽기 액세스를 차단하지 않습니다. 제약 조건을 추가하면 (모든 선택이 차단되도록)이 ALTER TABLE을 통해 추가되기 때문에 테이블 잠금을 넣어 않습니다.

from https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index by cc-by-sa and MIT license