[ORACLE] UNIQUE INDEX, UNIQUE Constraint 동작

1. UNIQUE INDEX, UNIQUE Constraint DB별 동작차이

 

※ ORACLE

-. UNIQUE 제약조건 -> insert, update 문장 실행이 끝나고 중복확인
-. UNIQUE 인덱스 -> insert, update 문장 실행이 끝나고 중복확인

-. Oracle에서 Unique Constraint와 Unique Index가 모두 존재할 때, 데이터베이스는 Unique Constraint를 먼저 확인합니다.

CREATE TABLE TEST_USER01.TEST001
(
	c1 number   
);

insert into TEST_USER01.TEST001
select level from dual connect by level < 100000;

commit;

CREATE UNIQUE INDEX TEST_USER01.IX_TEST01_N1
ON TEST_USER01.TEST001 (c1);

update TEST_USER01.TEST001 set c1 = c1+1;
-> 수행됨


drop index TEST_USER01.IX_TEST01_N1;
ALTER TABLE TEST_USER01.TEST001 ADD CONSTRAINT UK_TEST01 UNIQUE (c1);

update TEST_USER01.TEST001 set c1 = c1+1;
-> 수행됨

그럼 Unique index 생성 시, Unique constrant는 따로 생성 안되는데, 이럴 경우 data DML할때 이점이 있나?
그냥 DB마다 Transcation 처리, 아키텍처 구조가 달라서 그런건가..

 

 

※ Infomix DB Logging 모드 테이블

-. UNIQUE 제약조건 -> insert, update 문장 실행이 끝나고 중복확인
-. UNIQUE 인덱스 -> row-by-row 건건이 실행할 때마다 중복 확인

CREATE TABLE TEST_USER01.TEST001
(
	c1 number   
);

insert into TEST_USER01.TEST001
select level from dual connect by level < 100000;

commit;

CREATE UNIQUE INDEX TEST_USER01.IX_TEST01_N1
ON TEST_USER01.TEST001 (c1);

update TEST_USER01.TEST001 set c1 = c1+1;

-> row by row로 건건히 실행 될 때 마다 중복을 확인 함으로 에러발생

 

※ Postgre
-. UNIQUE 제약조건 -> row-by-row 건건이 실행할 때마다 중복 확인
-. UNIQUE 인덱스 -> row-by-row 건건이 실행할 때마다 중복 확인

 

 

※ 참고

UNIQUE 제약조건과 UNIQUE 인덱스의 차이

 

 

2. PK, UNIQUE INDEX, UNIQUE CONSTRAINT 생성에 따른 DD table 정보 조회

 

1. pk 생성시 unique index 자동생성 됨 

CREATE TABLE TEST_USER01.TEST001
(
	c1 number,
	c2 varchar2(100),
	c3 varchar2(100),
	c4 varchar2(100)
    
);

ALTER TABLE TEST_USER01.TEST001 ADD CONSTRAINT PK_TEST01 PRIMARY KEY(C1);

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from all_constraints
where table_name = 'TEST001';

OWNER                     CONSTRAINT_NAME           CONSTRAINT_TYPE           TABLE_NAME                INDEX_OWNER               INDEX_NAME
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
TEST_USER01               PK_TEST01                 P                         TEST001                   TEST_USER01               PK_TEST01


select OWNER,INDEX_NAME,UNIQUENESS,TABLE_OWNER,TABLE_NAME,TABLE_TYPE from dba_indexes
where table_name = 'TEST001';

OWNER                     INDEX_NAME                UNIQUENESS                TABLE_OWNER               TABLE_NAME                TABLE_TYPE
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
TEST_USER01               PK_TEST01                 UNIQUE                    TEST_USER01               TEST001                   TABLE

 

※ PK drop 시, index도 같이 drop되는지

[TIBERO] Constraint , index 제거

 

2. Unipue Constraint 생성시, index 생성 유무 확인
-. 생성됨

ALTER TABLE TEST_USER01.TEST001 ADD CONSTRAINT UK_TEST01 UNIQUE (c1,c2,c3);

SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from all_constraints
where table_name = 'TEST001';

OWNER                     CONSTRAINT_NAME           CONSTRAINT_TYPE           TABLE_NAME                INDEX_OWNER               INDEX_NAME
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
TEST_USER01               UK_TEST01                 U                         TEST001                   TEST_USER01               UK_TEST01

SQL> 
SQL> select OWNER,INDEX_NAME,UNIQUENESS,TABLE_OWNER,TABLE_NAME,TABLE_TYPE from dba_indexes
where table_name = 'TEST001';

OWNER                     INDEX_NAME                UNIQUENESS                TABLE_OWNER               TABLE_NAME                TABLE_TYPE
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
TEST_USER01               UK_TEST01                 UNIQUE                    TEST_USER01               TEST001                   TABLE

 

3. Unique index만 생성하면, constraint는 따로 생성해줘야한다.

CREATE UNIQUE INDEX IX_TEST001_N1 ON TEST_USER01.TEST001(C1,C2,C3);

ALTER TABLE TEST_USER01.TEST001 ADD CONSTRAINT UK_TEST01 UNIQUE (c1,c2,c3);


-- 따로 생성할 경우 unique constraint drop 한다고 index가 같이 drop되지는 않는다.
alter table TEST_USER01.TEST001 drop constraint UK_TEST01;