Блог

Главная - Вопросы по программированию - Изменение ограничения для фильтрации

Изменение ограничения для фильтрации

#sql #sql-server #where-clause #unique-constraint #unique-index

#sql #sql-сервер #where-предложение #уникальное ограничение #уникальный-индекс

Вопрос:

Я изучаю возможность преобразования ранее существующего ограничения в моей базе данных в фильтруемое ограничение. Это ограничение существует в настоящее время:

 ALTER TABLE [dbo].[ALIAS] ADD  CONSTRAINT [AK_ALIAS001] UNIQUE NONCLUSTERED 
(
    [ASSIGNEDBY] ASC,
    [ROLETYPE] ASC,
    [CODEALIAS] ASC,
    [ALIASASSIGNEDBY] ASC
)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)

ON [PRIMARY]
GO

Я бы хотел не потерять функциональность, но теперь, когда я добавляю новое значение ALIASASSIGNEDBY, мне нужно иметь возможность иметь неуникальные значения для этих полей, если значение ALIASASSIGNEDBY отличается от 1.

Из моего чтения я думаю, что смогу сделать это ограничение условным, но я не уверен, как это сделать.

Ответ №1:

То, как я читаю ваше требование, вы хотите обеспечить уникальность для этого кортежа в наборе where ALIASASSIGNEDBY = 1 (и, соответственно, если ALIASASSIGNEDBY <> 1 что-то идет).

Если у меня есть вышеуказанное правильное, вот как я бы это сделал:

 create unique index [UIX_ALIAS001] on [dbo].[ALIAS]
   ([ASSIGNEDBY], [ROLETYPE], [CODEALIAS], [ALIASASSIGNEDBY])
   WHERE [ALIASASSIGNEDBY] = 1;

Пара замечаний:

  1. Поскольку этот индекс теперь применяется только к подмножеству данных, запросы, которые использовали предыдущий индекс (неявно созданный из уникального ограничения), могут больше не иметь возможности использовать новый. Что-то нужно проверить!
  2. Вам может не понадобиться [ALIASASSIGNEDBY] в качестве ключевого столбца в индексе. То, как я его определил, все строки, которые подпадают под фильтр, будут иметь одинаковое значение, и поэтому оно избыточно с точки зрения обеспечения уникальности.
  3. Если вы используете версию SQL, которая позволяет это, рассмотрите возможность создания онлайн-индекса, чтобы избежать блокировки таблицы на время.
  4. Если вы используете версию SQL, которая позволяет это, рассмотрите возможность добавления сжатия данных в индекс. по моему опыту, это почти всегда хорошая идея ™.

Комментарии:

1. Спасибо за подробности! Я рассмотрю пункты 3 и 4, для пункта 1, как мне это протестировать?

2. Это один из случаев, когда просто попробовать это безопасно. Если функции включены, это позволит вам продолжить. Если вы этого не сделаете, это выдаст вам ошибку и не внесло никаких изменений.

Ответ №2:

Вы можете сделать это с помощью уникального отфильтрованного индекса:

 CREATE UNIQUE INDEX [IDX_ALIAS001] 
    ON [dbo].[ALIAS] ([ASSIGNEDBY], [ROLETYPE], [CODEALIAS], [ALIASASSIGNEDBY])
    WHERE [ALIASASSIGNEDBY] <> 1