Уникальное ограничение 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". Я совершенно смущен этим поведением. Есть ли ограничение или нет?

Существует много статей, объясняющих, почему возможно иметь уникальное ограничение без уникального индекса; это понятно и имеет смысл. Однако я не понимаю причину уникального индекса без ограничений.

Ответ 1

Ограничение и индекс - это отдельные логические сущности. Например, уникальное ограничение отображается в 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.

Но если мы говорим о прямом уникальном индексе, основанном на функциях, вероятно, относительно мало случаев, когда на самом деле имеет смысл создавать индекс, а не создавать ограничение. С другой стороны, относительно мало случаев, когда на практике это имеет большое значение. Вы почти никогда не хотели бы объявлять ограничение внешнего ключа, которое ссылалось на уникальное ограничение, а не на ограничение первичного ключа, поэтому вы редко теряете что-то, только создавая индекс и не создавая ограничение.

Ответ 2

Еще один момент, который может быть полезен в этом контексте: Отключение/удаление существующего уникального ограничения не отбрасывают базовый уникальный индекс. Вы должны явно отказаться от уникального индекса.