Когда следует использовать уникальное ограничение вместо уникального индекса?

Когда я хочу, чтобы столбец имел разные значения, я могу либо использовать ограничение

создать таблицу t1 (
id int первичный ключ,
код varchar (10) уникальный NULL
);
идти

, или я могу использовать уникальный индекс

создать таблицу t2 (
id int первичный ключ,
код varchar (10) NULL
);
идти

создать уникальный индекс I_t2 по t2 (код);

Столбцы с уникальными ограничениями кажутся хорошими кандидатами для уникальных индексов.

Есть ли какие-либо известные причины для использования уникальных ограничений, а не для использования уникальных индексов?

160 голосов | спросил bernd_k 4 Jpm1000000pmTue, 04 Jan 2011 13:32:27 +030011 2011, 13:32:27

6 ответов


128

Под капотом уникальное ограничение реализуется так же, как уникальный индекс - необходим индекс для эффективного выполнения требования о принудительном принуждении. Даже если индекс создается в результате ограничения UNIQUE, планировщик запросов может использовать его, как и любой другой индекс, если он видит его как лучший способ приблизиться к заданному запросу.

Итак, для базы данных, которая поддерживает обе функции, выбор для использования которых часто сводится к предпочтению стиля и последовательности.

Если вы планируете использовать индекс в качестве индекса (т. е. ваш код может полагаться на поиск /сортировку /фильтрацию в этом поле, чтобы быть быстрым), я бы явно использовал уникальный индекс (и комментировал источник), а не ограничение чтобы сделать это понятным - таким образом, если требование уникальности будет изменено в более поздней редакции приложения, которое вы (или какой-либо другой кодер) узнаете, чтобы убедиться, что вместо уникального индекса помещен уникальный код (просто удалив уникальный ограничение полностью удалит индекс). Также конкретный индекс можно назвать в подсказке индекса (т.е. WITH (INDEX (ix_index_name)), который, как мне кажется, не соответствует индексу, созданному за кулисами для управления уникальностью, поскольку вы вряд ли узнаете его имя.

Точно так же, если вам нужно только обеспечить уникальность как бизнес-правило, а не поле, которое нужно искать или использовать для сортировки, я бы использовал ограничение, чтобы снова сделать предполагаемое использование более очевидным, когда кто-то еще смотрит на ваш определение таблицы.

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

ответил David Spillett 4 Jpm1000000pmTue, 04 Jan 2011 14:27:15 +030011 2011, 14:27:15
85

В дополнение к точкам в других ответах, вот некоторые ключевые различия между ними.

Примечание. Сообщения об ошибках относятся к SQL Server 2012.

Ошибки

Нарушение уникального ограничения возвращает ошибку 2627.

Msg 2627, уровень 14, состояние 1, строка 1
Нарушение ограничения UNIQUE KEY «P1U_pk». Невозможно вставить дубликат ключа в объект 'dbo.P1U'. Значение дублирующегося ключа равно (1).
Заявление было прекращено.

Нарушение уникального индекса возвращает ошибку 2601.

Msg 2601, уровень 14, состояние 1, строка 1
Невозможно вставить повторяющуюся строку ключа в объект «dbo.P1» с уникальным индексом «P1_u». Значение дублирующегося ключа равно (1).
Заявление было прекращено.

Отключение

Невозможно отключить уникальное ограничение.

Msg 11415, уровень 16, состояние 1, строка 1
Объект 'P1U_pk' не может быть отключен или включен. Это действие применяется только к внешним ключам и ограничениям проверки.
Msg 4916, уровень 16, состояние 0, строка 1
Не удалось включить или отключить ограничение. См. Предыдущие ошибки.

Но уникальный индекс за ограничением первичного ключа или уникальным ограничением может быть отключен, как и любой уникальный индекс. Hat-tip Brain2000.

ALTER INDEX P1_u ON dbo.P1 DISABLE;

Обратите внимание на обычное предупреждение о том, что отключение кластерного индекса делает данные недоступными.

Функции

Уникальные ограничения поддерживают параметры индексирования, такие как FILLFACTOR и IGNORE_DUP_KEY, хотя это не относится к каждой версии SQL Server.

Включенные столбцы

Некластеризованные индексы могут включать неиндексированные столбцы (называемые индексом покрытия, это значительное повышение производительности). Индексы позади PRIMARY KEY и UNIQUE ограничений не могут содержать столбцы. Hat-tip @ypercube.

Фильтрация

Невозможно отфильтровать уникальное ограничение.

Можно отфильтровать уникальный индекс.

СОЗДАТЬ УНИКАЛЬНЫЙ НЕПРЕРЫВНЫЙ ИНДЕКС Students6_DrivesLicence_u
ON dbo.Students6 (DriversLicenceNo) WHERE DriversLicenceNo не является нулевым;

Ограничения внешнего ключа

Ограничение внешнего ключа не может ссылаться на отфильтрованный уникальный индекс, хотя он может ссылаться на нефильтрованный уникальный индекс (я думаю, что это было добавлено в SQL Server 2005).

Нейминг

При создании ограничения указывать имя ограничения необязательно (для всех пяти типов ограничений). Если вы не укажете имя, то MSSQL будет генерировать его для вас.

CREATE TABLE dbo.T1 (
    TID int not null ПЕРВИЧНЫЙ КЛЮЧ
);
ИДТИ
CREATE TABLE dbo.T2 (
    TID int not null CONSTRAINT T2_pk ПЕРВИЧНЫЙ КЛЮЧ
);

При создании индексов вы должны указать имя.

Hat-tip @ i-one.

Ссылки

http://technet.microsoft. ком /EN-US /библиотека /aa224827 (v = SQL.80) .aspx

http://technet.microsoft.com/en-us/library /ms177456.aspx

ответил Greenstone Walker 17 TueEurope/Moscow2013-12-17T01:06:46+04:00Europe/Moscow12bEurope/MoscowTue, 17 Dec 2013 01:06:46 +0400 2013, 01:06:46
5

Процитировать MSDN как авторитетный источник:

  

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

И ...

  

Механизм базы данных автоматически создает индекс UNIQUE для обеспечения соблюдения   требование единственности ограничения UNIQUE. Следовательно, если   попытка вставить дублируемую строку, возвращается Database Engine   сообщение об ошибке, в котором указано, что ограничение UNIQUE нарушено   и не добавляет строку в таблицу. Если кластерный индекс   явно заданный, уникальный, некластеризованный индекс создается   default для принудительного ограничения UNIQUE ... более подробная информация здесь

Другое в: https: //technet .microsoft.com /EN-US /библиотека /aa224827% 28В = sql.80% 29.aspx

ответил user919426 21 J0000006Europe/Moscow 2016, 11:21:25
3

Одно из основных различий между уникальным ограничением и уникальным индексом заключается в том, что ограничение внешнего ключа в другой таблице может ссылаться на столбцы, которые составляют уникальное ограничение. Это не относится к уникальным индексам. Кроме того, уникальные ограничения определяются как часть стандарта ANSI, а индексы - нет. Наконец, уникальное ограничение, которое считается живущим в области проектирования логической базы данных (которое может быть реализовано по-разному с помощью разных механизмов БД), в то время как индекс является физическим аспектом. Поэтому уникальное ограничение является более декларативным. Я бы предпочел уникальное ограничение почти во всех случаях.

ответил Dmitry Frenkel 3 Mayam12 2012, 00:10:20
1

В Oracle существенное различие заключается в том, что вы можете создать уникальный для функции индекс, который нельзя выполнить с помощью уникальных ограничений:

Например

создать уникальный индекс ux_test на my_table (случай, когда сумма! = 0, а затем fk_xyz);

Так что fk_xyz уникален только для записи, у которой amount! = 0.

ответил Amir Pashazadeh 16 MonEurope/Moscow2013-12-16T22:09:50+04:00Europe/Moscow12bEurope/MoscowMon, 16 Dec 2013 22:09:50 +0400 2013, 22:09:50
-3

УНИКАЛЬНОЕ ограничение предпочтительнее, чем UNIQUE Index. Когда ограничение не уникально, вам нужно использовать обычный или не уникальный индекс. Ограничение также является другим типом индекса. Индекс используется для быстрого доступа.

Уникальные индексы могут содержать предложения. Например, вы можете создавать индексы для каждого года на основе столбца даты

WHERE Sale_Date BETWEEN '2012-01-01' AND '2012-12-31'
ответил Ravi Ramaswamy 5 AMpFri, 05 Apr 2013 00:32:32 +040032Friday 2013, 00:32:32

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132