[Oracle] 오라클 컬럼 UNIQUE 추가 및 인덱스 생성 방법

오라클에서 테이블 컬럼의 값을 중복되지 않도록 제한하고 싶을 때는 UNIQUE 제약조건을 사용한다. ALTER TABLE 명령어를 통해 기존 테이블에도 UNIQUE 제약조건을 추가하거나 삭제할 수 있다. UNIQUE 제약조건이 추가된 이후에 해당 컬럼에 중복된 값을 INSERT 하거나 UPDATE 하려고 하면 "ORA-00001: 유일성(무결성) 제약조건에 위배됩니다" 오류가 발생한다.

 

목차 컬럼 UNIQUE 제약조건 추가 방법 컬럼 UNIQUE 제약조건과 인덱스를 동시에 추가 방법 (권장) 컬럼 UNIQUE 제약조건 제거 방법 테이블 생성시 UNIQUE 제약조건 설정 방법 테이블 UNIQUE 제약조건과 인덱스 조회 방법

 

컬럼 UNIQUE 제약조건 추가 방법

ALTER TABLE scott.emp ADD CONSTRAINT uq_empno_deptno UNIQUE(empno, deptno);

 

ALTER TABLE [스키마명].[테이블명]  ADD CONSTRAINT [유니크명] UNIQUE([컬럼], [컬럼] ...);

 

위의 명령어로 UNIQUE 제약조건을 추가할 수 있으며, UNIQUE 인덱스가 자동으로 생성된다. UNIQUE 인덱스명을 원하는 형식으로 지정하여 생성하고 싶을 때는 UNIQUE 인덱스를 먼저 생성하고 UNIQUE 제약조건을 추가해야 한다.

 

※ 아래 인덱스를 지정하여 UNIQUE 제약조건을 추가하는 방법을 권장한다.

 

컬럼 UNIQUE 제약조건과 인덱스를 동시에 추가 방법 (권장)

-- 1.먼저 인덱스 생성
CREATE UNIQUE INDEX uq_empno_deptno_idx ON scott.emp(empno, deptno);

-- 2.인덱스를 UNIQUE 제약조건 추가
ALTER TABLE scott.emp ADD CONSTRAINT uq_empno_deptno UNIQUE(empno, deptno)
USING INDEX uq_empno_deptno_idx;

 

실무에서 깔끔하게 관리하고자 한다면, 인덱스를 먼저 생성하고 UNIQUE 제약조건에 인덱스를 연결하여 생성하는 방법을 권장한다.

 

컬럼 UNIQUE 제약조건 제거 방법

ALTER TABLE scott.emp DROP CONSTRAINT uq_empno_deptno;

 

ALTER TABLE [스키마명].[테이블명]  DROP CONSTRAINT [유니크명];

 

테이블 생성시 UNIQUE 제약조건 설정 방법

CREATE TABLE scott.emp 
( 
    empno       NUMBER(4)  NOT NULL,
    ename       VARCHAR2(10),
    job         VARCHAR2(9),
    mgr         NUMBER(4),
    hiredate    DATE,
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(2),
    CONSTRAINT uk_empno_deptno UNIQUE(empno, deptno)
);

 

CREATE TABLE 구문에 CONSTRAINT [유니크명] UNIQUE( [컬럼], [컬럼] ...)을 추가하여 UNIQUE 제약조건을 추가할 수 있다. 그러나 위의 방법을 사용하면 오라클에서 임의로 UNIQUE 인덱스명이 생성되기 때문에 권장하지 않는다.

 

CREATE TABLE scott.emp 
( 
    empno       NUMBER(4)  NOT NULL,
    ename       VARCHAR2(10),
    job         VARCHAR2(9),
    mgr         NUMBER(4),
    hiredate    DATE,
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(2)
);

CREATE UNIQUE INDEX uq_empno_deptno_idx ON scott.emp(empno, deptno);

ALTER TABLE scott.emp ADD CONSTRAINT uq_empno_deptno UNIQUE(empno, deptno)
USING INDEX uq_empno_deptno_idx;

 

CREATE TABLE 구문과 UNIQUE 제약조건 구분을 분리하여 생성하는 것을 권장한다.

 

테이 UNIQUE 제약조건과 인덱스 조회 방법

SELECT *
  FROM all_constraints
 WHERE table_name = 'EMP'
   AND constraint_type = 'U'

 

해당 테이블에 UNIQUE 제약조건을 조회할 수 있다.

 

SELECT uc.constraint_name AS "제약조건명"
     , ucc.column_name    AS "컬럼명"
     , ucc.position       AS "위치"
     , ui.uniqueness      AS "유니크여부"
     , uc.status          AS "상태"
     , ui.index_name      AS "인덱스명"
  FROM all_constraints uc
  JOIN all_cons_columns ucc 
    ON uc.constraint_name = ucc.constraint_name
  JOIN all_indexes ui 
    ON uc.index_name = ui.index_name
 WHERE uc.table_name = 'EMP'
   AND uc.constraint_type = 'U'
 ORDER BY uc.constraint_name, ucc.position

 

 

위의 쿼리를 사용하면 해당 테이블의 UNIQUE 제약조건을 조금 더 상세하고 조회할 수 있다.

 

사용자의 권한에 따라서 시스템뷰를 바꿔서 사용해야 하는 경우가 있으니 참고하길 바란다.

(권한이 없을 경우 아래의 시스템뷰로 바꿔서 조회)

all_constraints → user_constraints

all_cons_columns → user_indexes

all_indexes → user_indexes