Oracle 고유 제약 조건 및 고유 인덱스

Oracle 고유 제약 조건 및 고유 인덱스

고유 제약(Oracle) 없이 고유 인덱스를 보유하는 목적이 무엇인지 누가 명확히 할 수 있습니까?예를들면,

create table test22(id int, id1 int, tmp varchar(20));
create unique index idx_test22 on test22(id);
insert into test22(id, id1, tmp) values (1, 2, 'aaa'); // ok
insert into test22(id, id1, tmp) values (1, 2, 'aaa'); // fails, ORA-00001: unique   
  // constraint (TEST.IDX_TEST22) violated

아직까지는 제약이 있는 것처럼 보인다.그렇지만

create table test33(id int not null primary key, 
test22_id int not null, 
foreign key(test22_id) references test22(id) );

하는 경우도 있습니다."ORA-02270: no matching unique or primary key for this column-list" 없나요?약이있있,,,??

고유 인덱스 없이 고유 제약 조건을 가질 수 있는 이유를 설명하는 많은 기사가 있습니다. 이는 명확하고 완벽한 의미입니다.하지만 저는 제약 없이 고유 인덱스를 사용하는 이유를 이해할 수 없습니다.

제약 조건과 인덱스는 별개의 논리 엔티티입니다. 독특한 예,들 a, a a a a a a a a a a a a에 .USER_CONSTRAINTS (오류)ALL_CONSTRAINTS ★★★★★★★★★★★★★★★★★」DBA_CONSTRAINTS됩니다.USER_INDEXES (오류)ALL_INDEXES ★★★★★★★★★★★★★★★★★」DBA_INDEXES를 참조해 주세요.

고유 구속조건은 고유하지 않은 인덱스를 사용하여 고유 구속조건을 적용할 수 있지만 인덱스에 의해 강제됩니다.예를 들어 고유하지 않은 인덱스를 사용하여 지연 가능한 고유 제약이 적용됩니다.열에 고유하지 않은 인덱스를 작성한 후 고유한 제약 조건을 작성하는 경우 해당 고유하지 않은 인덱스를 사용하여 고유한 제약 조건을 적용할 수도 있습니다.

실제로 고유한 인덱스는 고유한 제약 조건의 구현이 인덱스를 사용하기 때문에 고유한 제약 조건이 발생시키는 것과 동일한 오류를 발생시킨다는 점에서 고유한 지연 불가능한 제약 조건과 매우 유사합니다.하지만 그것은 제약이 없기 때문에 완전히 같지는 않다.앞에서 설명한 바와 같이 고유한 제약조건이 없으므로 열을 참조하는 외부 키 제약조건을 생성할 수 없습니다.

고유한 구속조건을 생성할 수 없는 고유한 인덱스를 생성할 수 있는 경우가 있습니다.예를 들어 조건부 고유성을 적용하는 함수 기반 인덱스입니다.는, 「」를 확인해 .COL1는 모든 행에 고유합니다.

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE t (
  2    col1 number,
  3    deleted_flag varchar2(1) check( deleted_flag in ('Y','N') )
  4* )
SQL> /

Table created.

SQL> create unique index idx_non_deleted
  2      on t( case when deleted_flag = 'N' then col1 else null end);

Index created.

SQL> insert into t values( 1, 'N' );

1 row created.

SQL> insert into t values( 1, 'N' );
insert into t values( 1, 'N' )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_NON_DELETED) violated


SQL> insert into t values( 1, 'Y' );

1 row created.

SQL> insert into t values( 1, 'Y' );

1 row created.

하지만 고유하고 기능적이지 않은 인덱스를 말하는 경우 제약조건을 만드는 것보다 인덱스를 만드는 것이 더 합리적인 경우는 상대적으로 적습니다.한편, 실제로 큰 차이를 보이는 경우는 비교적 적다.기본 키 제약 조건이 아닌 고유한 제약 조건을 참조하는 외부 키 제약 조건을 선언하는 일은 거의 없을 것입니다. 따라서 인덱스만 만들고 제약 조건을 만들지 않음으로써 손실되는 것이 거의 없습니다.

이미 다른 답변에서 설명한 바와 같이 제약조건과 인덱스는 서로 다른 엔티티입니다.그러나 그들은 이 주제에 대한 정확한 정의와 공식적인 언급이 부족하다.이 두 실체의 관계를 논하기 전에 서로 독립적으로 목적을 살펴보자.

제약의¹ 목적:

데이터베이스의 값을 제한하는 규칙인 무결성 제약 조건을 정의하려면 제약 조건을 사용합니다.

인덱스의² 목적:

열에 인덱스를 만들어 쿼리 속도를 높일 수 있습니다.인덱스는 테이블 행의 작은 부분을 반환하는 작업을 위해 데이터에 더 빨리 액세스할 수 있도록 합니다.

일반적으로 다음과 같은 경우 열에 인덱스를 생성해야 합니다.

• 열은 자주 쿼리됩니다.
• 열에 참조 무결성 제약 조건이 있습니다.
• 열에 고유 키 무결성 제약 조건이 있습니다.

이제 제약 조건과 인덱스가 무엇인지 알게 되었습니다. 하지만 이러한 제약 조건과 인덱스는 어떤 관계가 있을까요?

인덱스와 제약조건의³ 관계는 다음과 같습니다.

• 제약조건은 인덱스를 생성하거나 기존 인덱스를 사용하여 효율적으로 적용할 수 있습니다.예를 들어 PRIMAY KEY 제약조건은 인덱스를 작성하거나(일관 또는 비일관) 기존의 적합한 인덱스를 찾아 사용합니다.

• 인덱스는 제약 조건과 무관합니다.인덱스는 인덱스입니다.

따라서 제약조건이 생성/사용 및 색인화할 수 있습니다. INDEX는 INDEX, 그 이상도 이하도 아닙니다.

이를 요약하고 질문에서 다음 문장을 직접 다루십시오.

하지만 저는 제약 없이 고유 인덱스를 사용하는 이유를 이해할 수 없습니다.

인덱스는 쿼리 및 무결성 검사(제한조건) 속도를 높입니다.또한 조건부 고유성을 위해 고유한(기능적) 인덱스가 사용됩니다. 이는 제약조건으로는 달성할 수 없기 때문입니다.

이것으로 토픽 전체가 좀 더 명확해졌으면 좋겠다고 생각합니다만, 원래의 질문에는 아직 답이 남아 있지 않은 측면이 있습니다.

제약조건이 존재하지 않는데 다음 오류가 발생한 이유는 무엇입니까?

ORA-00001: 고유 제약(테스트).IDX_TEST22) 위반

답은 간단합니다. 제약이 없으며 오류 메시지에 잘못된 이름이 표시됩니다.

동일한 문제에 대한 공식 "Oracle Ask TOM" 코멘트를 참조하십시오.

그것은 제약이 아니다.에러 메세지가 표시됩니다.
제약사항인 경우, 외부 키를 작성할 수 있지만 작성할 수 없습니다.

도움이 됐으면 좋겠다.

링크:

¹ 제약사항에 관한 Oracle 10g 문서

² 인덱스 전략 선택에 관한 Oracle 10g 문서

^{3 4} 유사한 문제에 대한 "Oracle Ask TOM" 응답

이 컨텍스트에서 유용할 수 있는 또 다른 포인트는 기존 고유 제약 조건을 비활성화/폐기해도 기본 고유 인덱스는 폐기되지 않습니다.고유 인덱스를 명시적으로 드롭해야 합니다.

고유한 구속조건을 선언하여 조건부 고유성을 만들 수 없지만 고유한 인덱스를 선언하여 고유성을 만들 수 있습니다.

아래를 실행하려는 경우 지원:

alter table test22 
add constraint test22_u 
unique (id, case when tmp = 'aaa' then null else tmp end);

ORA-00904: : invalid identifier 

하지만 만약 당신이 고유 인덱스를 사용해서 할 수 있습니다.

create unique index test22_u 
on test22 ( customer_id, 
case when is_default = 'Y' then null else address_id end)

언급URL : https://stackoverflow.com/questions/7521817/oracle-unique-constraint-and-unique-index