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", Я полностью смущен этим поведением. Есть ограничение или нет?
Есть много статей, которые объясняют, почему возможно иметь уникальное ограничение без уникального индекса; это ясно и имеет смысл. Однако я не понимаю причину уникального индекса без ограничений.
3 ответа
Ограничение и индекс являются отдельными логическими объектами. Например, уникальное ограничение видно в 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.
Но если мы говорим о прямом уникальном нефункциональном индексе, то, вероятно, существует относительно немного случаев, когда на самом деле имеет смысл создавать индекс, а не создавать ограничение. С другой стороны, есть относительно немного случаев, когда это имеет большое значение на практике. Вы почти никогда не захотите объявлять ограничение внешнего ключа, которое ссылается на ограничение уникальности, а не ограничение первичного ключа, поэтому вы редко что-то теряете, только создавая индекс, а не создавая ограничение.
Как уже было объяснено в других ответах: ограничения и индексы - это разные сущности. Но им не хватает точных определений и официальных комментариев по теме. Прежде чем обсуждать отношения между этими двумя сущностями, давайте посмотрим на их назначение независимо друг от друга.
Цель ограничения1:
Используйте ограничение для определения ограничения целостности - правила, ограничивающего значения в базе данных.
Цели индекса2:
Вы можете создавать индексы по столбцам, чтобы ускорить запросы. Индексы обеспечивают более быстрый доступ к данным для операций, которые возвращают небольшую часть строк таблицы.
Как правило, вы должны создавать индекс для столбца в любой из следующих ситуаций:
- Столбец часто запрашивается.
- Для столбца существует ограничение ссылочной целостности.
- В столбце существует ограничение целостности ключа UNIQUE.
Теперь мы знаем, что такое ограничения и индексы, но какова связь между ними?
Связь между индексами и ограничениями равна3:
ограничение МОЖЕТ создать индекс или использовать существующий индекс для эффективного обеспечения себя. Например, ограничение PRIMARY KEY либо создаст индекс (в зависимости от уникальности или неуникальности), либо найдет существующий подходящий индекс и будет использовать его.
индекс не имеет ничего общего с ограничением. Индекс - это индекс.
Итак, ограничение МОЖЕТ создавать / использовать и индексировать. ИНДЕКС - это ИНДЕКС, не больше и не меньше.
Итак, суммируйте это и прямо обратитесь к следующему предложению из вашего вопроса:
Однако я не понимаю причину уникального индекса без ограничений.
Индексы ускоряют запросы и проверки целостности (ограничения). Также для условной уникальности используется уникальный (функциональный) индекс, поскольку этого нельзя достичь с помощью ограничения.
Надеюсь, это внесет немного больше ясности во всю тему, но есть один аспект исходного вопроса, который остается без ответа:
Почему при отсутствии ограничений возникла следующая ошибка:
ORA-00001: уникальное ограничение (TEST.IDX_TEST22) нарушено
Ответ прост: ограничений нет, и сообщение об ошибке неправильно называет его!
См. Официальный комментарий 4 "Oracle Ask TOM" по той же проблеме:
Это не ограничение. сообщение об ошибке "неправильно называет" это.
Если бы это было ограничение, вы могли бы создать для него внешний ключ, но не можете.
Надеюсь, это поможет.
Ссылки:
1 Документация Oracle 10g по ограничениям
Еще один момент, который может быть полезен в этом контексте: отключение / удаление существующего уникального ограничения не приводит к удалению базового уникального индекса. Вы должны отбросить уникальный индекс явно.
Вы не можете сделать условную уникальность, объявив уникальное ограничение, но вы можете сделать это, объявив уникальный индекс.
Поддержите, если вы попытаетесь выполнить ниже:
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)