В чем принципиальное отличие unique (constraints) от unique index?

Question

[arturich]

Здравствуйте, делал много подходов к пониманию этой темы — не помогло.

Прочитал документацию и не понял разницы:

www.postgresql.org/docs/9.1/static/ddl-constraints.html
www.postgresql.org/docs/9.1/static/sql-createindex.html

Объясните пожалуйста в чем принципиальное отличие?

Answer 1

[Алексей Сундуков]

Разница в том, что ограничения (сonstraints) призваны обеспечивать целостность данных, а индексы (index) — скорость доступа к данным. Это две абсолютно не связанные сущности. Причем если первое — часть SQL стандарта, то второе нет (ибо ни как не связанно с функциональностью языка, введение индексов — вынужденная мера). Разработчик сам решает, в каких случая применить эти механизмы и использование одного вовсе не требует использование другого.

Теперь касательно уникальности (unique). В данном случае при добавлении ограничения уникальности (unique constraint) Postgresql сам навешивает на указанное поле индекс. Это просто особенность реализации в данной СУБД. Разработчики решили, что вот так оно будет работать и все тут (причем небезосновательно). В другой же схожей ситуации они решили, что разработчик сам думает, нужно ли ему использовать этих два механизма вместе, или нет. Я говорю об ограничении целостности по внешнему ключу (foreign key). В Postgresql индексы по полям с данным видом ограничения не создаются (Индексы по внешним ключам в Postgresql). А, к примеру, в MySQL создаются. Это особенность реализации в MySQL.

Поэтому важно просто понимать, что это не связанные вещи, просто в некоторых реализациях они «сцеплены» между собой и создание некоторых видов ограничений приводит к автоматическому созданию индекса.

Comments

[ztxn]

Согласен с предыдущим оратором, хотел бы его ответ немного дополнить.

Индекс это лишь механизм, посредством которого производится контроль ограничения. Именно для этих целей в ряде(в большинстве) случаев он сам создается. Оракл для контроля ограничения может использовать и не уникальный, и составной индекс, если контроллируемое поле в индексе упомянуто первым. Не знаю как в PG, но, судя по наличию кляузы using index в alter table add constraint, PG вроде тоже должен это уметь.

[ztxn]

Кстати тоже тема, оракле, если сделать индекс unusable, то ограничение униклаьности все равно будет работать. Работать будет единственным доступным ему способом, оно будет просто запрещать модификцию контроллируемых им столбцов, не имея возможности проверить уникальность.

Answer 2

[Андрей Поляков]

Фактически создавая индекс вы создаёте ограничение и наоборот.

При создании ограничения нельзя управлять созданием индекса. При создании индекса — ограничением. Со всеми вытекающими.

На практике единственная разница с которой я встречался, это разница в тексте ошибок ;)

Comments

[arturich]

Ну собственно да, я пока что понял тоже самое. Просто думал — может есть какие-то подводные камни

Answer 3

[hom9k]

Принципиальных отличий нет. Просто на практие для «id»-like колонок лучше использовать unique constraint, а для колонок, использующихся в сортировках и where-условиях использовать index. Так просто намного понятнее и предсказуемее знакомиться с базой другому разработчику.

А в целом magnitudo прав
«Фактически создавая индекс вы создаёте ограничение и наоборот.»

Comments

[Алексей Сундуков]

Для «id»-like колонок лучше вовсе использовать PRIMARY KEY + SERIAL тип. Потому что на вставках не нужно делать проверку на unique constraint (ибо монотонность и целостность гарантируется СУБД и работает по типу счетчика и не нужно трясти Х записей с целью ответа на вопрос «уникально ли вставляемое значение»).

Answer 4

[Виктор Яковенко]

Вот здесь https://www.sql.ru/forum/665893/vopros-po-unique-c... нашел более конкретный (не философский) ответ.
"Вроде бы нашел...только при создании constraint появляется возможность создания внешнего ключа, а в остальном и unique constraint и unique index не допускают ввода повторяющихся значений.."