[이벤트 둘!/ 당첨자 발표및 정답공개] 당신의 오라클 내공을 보여주세요~

Oracle ACE "Dion Cho" 가 직접 출제하는 주옥같은 문제!
당신의 오라클 내공을 보여주세요~ 
가장 좋은 정답을 제출한 분께 5만원 상품권을 드립니다! (정답발표 아래로)

지금 바로 도전하세요~!

1. 아래와 같이 2 개의 Unique Constraint가 있습니다.

SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'T1';

CONSTRAINT_NAME C
--------------- -
T1_N1           U
T1_N2           U

2. 각 Constraint를 구성하는 컬럼은 다음과 같습니다.

SQL> col constraint_name format a15
SQL> col table_name format a15
SQL> col column_name format a15
SQL> 
SQL> select constraint_name, table_name, column_name
  2  from user_cons_columns
  3  where table_name = 'T1';

CONSTRAINT_NAME TABLE_NAME      COLUMN_NAME
--------------- --------------- ---------------
T1_N1           T1              C1
T1_N2           T1              C2

3. 각 컬럼을 구성하는 인덱스는 다음과 같습니다.

SQL> select i.index_name, c.column_name, i.uniqueness
  2  from user_indexes i, user_ind_columns c
  3  where i.table_name = 'T1'
  4  	  and i.index_name = c.index_name
  5  ;

INDEX_NAME           COLUMN_NAME     UNIQUENES
-------------------- --------------- ---------
T1_N1                C1              UNIQUE
T1_N2                C2              NONUNIQUE

여기서 문제!

왜 동일한 Unique Constraint 임에도 불구하고 인덱스 T1_N1은 Unique 인덱스이고, 인덱스 T1_N2는 Non-Unique 인덱스일까요?

최고의 답변을 선택하는 기준은 다음과 같습니다.

• 얼마나 빨리 답변하는가?
• 얼마나 정확하게 답변하는가?
• 재현 가능한 테스트 케이스를 제공하는가? (가산점)

답변은 quiz@ex-em.com 으로 보내주시면 됩니다.( 댓글은 금지되어있습니다^^;)

퀴즈 정답 접수가 마감되었습니다.

--------------------------------------------------------------------

<퀴즈 당첨자>

임**  ons***@gmail.com

본 퀴즈의 정답은 2개 입니다. 하지만 많은 분들께서 정답을 하나만 보내주셨는데요,^^; 그래도 하나의 정답을 가장 먼저 보내주신 분께 당첨의 영광을  드리도록 하겠습니다!

엑셈의 이벤트에 참여해주신 모든 분들께 진심으로 감사드립니다. 

2개의 정답이 아래에 공개됩니다!  

<퀴즈 정답>

이번 문제의 정답은 2개입니다.  

1. 이미 Non Unique 인덱스가 존재하는 컬럼에 대해 Unique Constraint를 부여하는 경우
2. Deferrable Constraint를 선언하는 경우

자세한 설명은 예제를 통해 하겠습니다.

1. Non Unique 인덱스를 먼저 만들고 Unique Constraint를 생성하는 경우입니다.

SQL> create table t1(c1 number);

Table created.

SQL> create index t1_n1 on t1(c1);

Index created.

SQL> alter table t1 add constraint t1_un unique(c1) using index t1_n1;

Table altered.

-- 또는 이렇게도 할 수 있습니다. 
SQL> alter table t1 add constraint t1_un unique(c1) using index(create index t1_n1 on t1(c1));

SQL> select index_name, uniqueness from user_indexes where table_name = 'T1';

INDEX_NAME           UNIQUENES
-------------------- ---------
T1_N1                NONUNIQUE

2. Unique Constraint를 Deferrable로 선언하는 경우입니다.

SQL> create table t1(c1 number);

Table created.

SQL> alter table t1 add constraint t1_un unique(c1) deferrable;

Table altered.

SQL> select index_name, uniqueness from user_indexes where table_name = 'T1';

INDEX_NAME           UNIQUENES
-------------------- ---------
T1_UN                NONUNIQUE

Deferrable이 가능하려면 Unique Index가 존재해서는 안됩니다. 따라서 어쩔 수 없이 Non Unique 인덱스를 만드는 것입니다. 단, Deferrable Constraint라고 하더라도 아직 Deferred된 상태는 아니므로 Constraint는 정상적으로 동작합니다.

SQL> insert into t1 values(1);

1 row created.

SQL> insert into t1 values(1);
insert into t1 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (TPACK.T1_UN) violated

다음과 같이 SET CONSTRAINT 명령을 통해서 명시적으로 Deferred 시킬 수 있습니다.(물론 Constraint 생성시에 Deferred 상태를 지정할 수도 있습니다)

SQL> set constraint t1_un deferred;

Constraint set.

SQL> insert into t1 values(1);

1 row created.

Commit할 때 에러가 발생합니다.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (TPACK.T1_UN) violated

Constraint를 생성할 때 NOVALIDATE 옵션을 부여하면 기존의 데이터들에 대해서는 Constraint를 확인하지 않습니다. 하지만 다음과 같이 Unique Constraint를 NOVALIDATE로 생성해도 Duplicate Key에러가 발생합니다.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1 values(1);

1 row created.

-- 중복값 추가!
SQL> insert into t1 values(1); 

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t1 add constraint t1_un unique(c1) enable novalidate;
alter table t1 add constraint t1_un unique(c1) enable novalidate
                              *
ERROR at line 1:
ORA-02299: cannot validate (TPACK.T1_UN) - duplicate keys found

왜 그럴까요? 힌트는 위에서 이미 제시되었습니다. Unique Constraint를 생성할 때 오라클은 가능한 한 Unique 인덱스를 만듭니다. 즉 위의 에러는 Unique 인덱스를 만드는 과정에서 생기는 것으로 볼 수 있습니다. 따라서 Unique 인덱스가 생기지 않게 하면 위의 에러를 피할 수 있을 것입니다.

즉 다음과 같이 Deferrable 속성을 부여하면 됩니다.

SQL> alter table t1 add constraint t1_un unique(c1) deferrable enable novalidate;

Table altered.

한가지 주의할 것은 이런 경우 Constraint을 지워도 인덱스는 남아있다는 것입니다.

SQL> alter table t1 drop constraint t1_un;

Table altered.

SQL> select index_name, uniqueness from user_indexes where table_name = 'T1';

INDEX_NAME           UNIQUENES
-------------------- ---------
T1_UN                NONUNIQUE

또는 다음과 같이 USING INDEX 구문을 이용해서 명시적으로 Non Unique 인덱스를 생성하면 됩니다.

SQL> drop index t1_un;

Index dropped.

SQL> alter table t1 add constraint t1_un unique(c1) using index(create index t1_n1 on t1(c1)) enable novalidate ;

Table altered.

기존의 중복값들은 그대로 남아있고(NOVALIDATE), 새롭게 추가되는 데이터에 대해서만 Unique Constraint가 적용됩니다.

SQL> insert into t1 values(1);
insert into t1 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (TPACK.T1_UN) violated


SQL> select * from t1;

        C1
----------
         1
         1