PK와 Unique Index

PK와 Unique Index간의 헷갈리는 부분이 있어서 포스팅하면서 정리해보도록 하겠습니다.

 

PK

PK는 Primary Key로써, 중복될 수 없는 유일한 단일값(Unique)을 가집니다. 그리고 값이 비어있지(Not Null) 않아야 합니다. Object 속성은 Constraint로써 제약조건으로 생성이 됩니다. 

(DBA_CONSTRAINTS의 뷰에서 확인할 수 있습니다.)

 

UNIQUE INDEX

Unique Index는 인덱스가 걸려있는 컬럼에 중복될 수 없는 유일한 값만을 보장하고, 인덱스가 걸려있는 컬럼에 Null값은 입력이 가능합니다. Object의 속성은 Index입니다.

 

그리고 테이블 당 PK는 하나입니다. UNIQUE INDEX는 여러개를 생성할 수 있습니다. (PK 컬럼에 인덱스가 생성되어 있지 않은 경우) PK 생성시 자동으로 UNIQUE INDEX가 생성됩니다.

 

PK를 생성하는 방법은 1.테이블 생성시 컬럼 레벨에서 생성하기, 2. 테이블 생성시 테이블 레벨에서 생성하기, 3.테이블 생성 후 alter table 명령어로 추가하기가 있습니다. 각 방법을 테스트를 통해 구문을 정리하겠습니다.

 

 

1.테이블 생성시 Primary Key 생성(컬럼레벨)

컬럼 뒤에 primary key 명령어를 추가하면 생성시 추가가 됩니다.

이름을 지정하지 않고 primary key로 생성시 SYS_Cxxxx형식으로 자동으로 이름이 부여되고 생성됩니다.

UNIQUE INDEX가 임의의 이름으로 생성됩니다.(제약조건과 동일한 이름)

SQL> create table PRIMARY_TEST
(A varchar2(10) primary key,
B varchar2(20),
C NUMBER
)
;

--제약조건 확인
select table_name,constraint_name,index_name,constraint_type,status from USER_CONSTRAINTS
where table_name = 'PRIMARY_TEST'

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME                     C STATUS  
------------------------------ ------------------------------ ------------------------------ - --------
PRIMARY_TEST                   SYS_C007098                    SYS_C007098                    P ENABLED 

--인덱스 확인
select index_name,index_type,table_name
,uniqueness
from user_indexes
where table_name='PRIMARY_TEST'

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
SYS_C007098                    NORMAL                      PRIMARY_TEST                   UNIQUE   

 

2.테이블 생성시 Primary Key 생성(테이블 레벨)

컬럼뒷부분이 아니라 constraint [PK명] primary key [PK컬럼] 형식으로 부여하여 생성할수도 있습니다.

이 경우 이름이 임의로 생성되는것이 아니라 직접 설정한 이름으로 생성되고 INDEX도 동일한 이름으로 생성됩니다.

 

create table PRIMARY_TEST2
(A varchar2(10) ,
B varchar2(20),
C NUMBER,
constraint PK_PRIMARY_TEST2 primary key (A)
)
;

--제약조건 확인
SQL> select table_name,constraint_name,index_name,constraint_type,status from USER_CONSTRAINTS
where table_name = 'PRIMARY_TEST2';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME                     C STATUS  
------------------------------ ------------------------------ ------------------------------ - --------
PRIMARY_TEST2                  PK_PRIMARY_TEST2               PK_PRIMARY_TEST2               P ENABLED 

1 rows selected.

--인덱스 확인
SQL> select index_name,index_type,table_name 
,uniqueness 
from user_indexes 
where table_name='PRIMARY_TEST2';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
PK_PRIMARY_TEST2               NORMAL                      PRIMARY_TEST2                  UNIQUE   

1 rows selected.

 

3.테이블 생성후 Primary Key 생성하기

일반 테이블 생성 후 alter table 명령어로 PK를 생성할 수 있습니다.

임의로 설정한 이름으로 인덱스와 PK가 생성됩니다. 테이블 생성시와 마찬가지로 add primary key 명령어를 사용하면 자동으로 이름이 생성됩니다.

CREATE TABLE primary_test3

(A varchar2(10) ,
B varchar2(20),
C NUMBER
)
;

--PK 추가
alter table primary_test3 add constraint PK_primary_test3 primary key (a);

--제약조건 확인
SQL> select table_name,constraint_name,index_name,constraint_type,status from USER_CONSTRAINTS
where table_name = 'PRIMARY_TEST3';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME                     C STATUS  
------------------------------ ------------------------------ ------------------------------ - --------
PRIMARY_TEST3                  PK_PRIMARY_TEST3               PK_PRIMARY_TEST3               P ENABLED 

1 rows selected.

--인덱스 확인
SQL> select index_name,index_type,table_name
,uniqueness
from user_indexes
where table_name='PRIMARY_TEST3';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
PK_PRIMARY_TEST3               NORMAL                      PRIMARY_TEST3                  UNIQUE   

1 rows selected.



CREATE TABLE primary_test4

(A varchar2(10) ,
B varchar2(20),
C NUMBER
)
;

--PK 생성
alter table primary_test4 add primary key (a);

--제약조건 확인
SQL> select table_name,constraint_name,index_name,constraint_type,status from USER_CONSTRAINTS
where table_name = 'PRIMARY_TEST4';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME                     C STATUS  
------------------------------ ------------------------------ ------------------------------ - --------
PRIMARY_TEST4                  SYS_C007101                    SYS_C007101                    P ENABLED 

1 rows selected.

--인덱스 확인
SQL> select index_name,index_type,table_name
,uniqueness
from user_indexes
where table_name='PRIMARY_TEST4';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
SYS_C007101                    NORMAL                      PRIMARY_TEST4                  UNIQUE   

1 rows selected.

 

+추가) 이미 컬럼에 인덱스가 생성되어 있는 상황에서 Primary Key 생성하기

PK를 생성하려는 컬럼에 이미 인덱스가 있을때에는 새로 인덱스를 생성하지 않고 기존에 있는 인덱스를 그대로 사용하며, Nonunique index인 경우에도 그대로 사용합니다.

(PK의 이름은 PK_PRIMARY_TEST5이고 인덱스의 이름은 IX_PRIMARY_TEST5입니다.)

--테이블 생성
CREATE TABLE primary_test5

(A varchar2(10) ,
B varchar2(20),
C NUMBER
)
;

--인덱스 생성(Nonunique)
create index ix_primary_test5 on primary_test5(a);

--인덱스 확인
SQL> select index_name,index_type,table_name
,uniqueness
from user_indexes
where table_name='PRIMARY_TEST5';
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
IX_PRIMARY_TEST5               NORMAL                      PRIMARY_TEST5                  NONUNIQUE


--제약조건 확인
SQL> select * from USER_CONSTRAINTS
where table_name like 'PRIMARY_TEST5%';

no row selected


--PK 추가
alter table primary_test5 add constraint pk_primary_test5 primary key (a);

--인덱스 확인
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
IX_PRIMARY_TEST5               NORMAL                      PRIMARY_TEST5                  NONUNIQUE


--제약조건 확인
SQL> select table_name,constraint_name,index_name,constraint_type,status from USER_CONSTRAINTS
where table_name = 'PRIMARY_TEST5';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME                     C STATUS  
------------------------------ ------------------------------ ------------------------------ - --------
PRIMARY_TEST5                  PK_PRIMARY_TEST5               IX_PRIMARY_TEST5               P ENABLED 

1 rows selected.

 

 

UNIQUE INDEX 생성하기

 

create table unique_test
(a varchar2(10),
b varchar2(20),
c number
)
;


create unique index ix_unique_test on unique_test(a);

--제약조건 확인
SQL> select table_name,constraint_name,index_name,constraint_type,status from USER_CONSTRAINTS
where table_name = 'UNIQUE_TEST';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME                     C STATUS  
------------------------------ ------------------------------ ------------------------------ - --------

0 rows selected.

--인덱스 확인
SQL> select index_name,index_type,table_name
,uniqueness
from user_indexes
where table_name='UNIQUE_TEST';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------
IX_UNIQUE_TEST                 NORMAL                      UNIQUE_TEST                    UNIQUE   

1 rows selected.

 

UNIQUE INDEX를 해당 컬럼에 not null조건까지 추가해서 만들면 PK와 기능은 동일합니다.(UNIQUE + NOT NULL)

하지만 FK와의 관계설정이 하나도 없기 때문에 사용자가 수동으로 데이터 간의 무결성 및 관계를 체크하면서 데이터를 관리할 수 밖에 없을 것 같습니다.

PK는 테이블간의 관계도 고려하여 생성한 것이기 때문입니다. 데이터의 참조무결성을 위해서는 PK를 테이블에 추가하여 관리하는 것이 맞는것 같습니다.