Индексирование ограничений FOREIGN KEY
Необходимость создать индекс внешних ключей часто возникает по следующим причинам:
С помощью ограничений FOREIGN KEY в связанных таблицах проверяются изменения ограничений PRIMARY KEY.
Столбцы внешних ключей часто используются для объединения критериев при соединении данных из связанных таблиц в запросах. Это делается путем сопоставления столбца или столбцов в ограничении FOREIGN KEY в одной таблице с одним или несколькими столбцами первичного или уникального ключей в другой таблице. Индекс позволяет компоненту Database Engine быстро находить связанные данные в таблице внешних ключей. Впрочем, создание индекса не является обязательным. Данные из двух связанных таблиц можно объединять, даже если в таблицах нет соответствующих ограничений PRIMARY KEY или FOREIGN KEY, однако связь внешних ключей между двумя таблицами означает, что эти две таблицы оптимизированы для соединения в запросе, где ключи используются в качестве критериев.
Количество ограничений FOREIGN KEY в таблице
В SQL Server не предусмотрено ни максимальное количество ограничений FOREIGN KEY в одной таблице (со ссылками на другие таблицы), ни максимальное число ограничений FOREIGN KEY из других таблиц, ссылающихся на одну таблицу. Тем не менее, фактическое максимальное число ограничений FOREIGN KEY ограничено конфигурацией оборудования и структуры базы данных и приложения. Рекомендуется, чтобы таблица содержала не более 253 ограничений FOREIGN KEY, а также чтобы на нее ссылалось не более 253 ограничений FOREIGN KEY. При разработке базы данных и приложений следует учитывать стоимость принудительных ограничений FOREIGN KEY.
Внешний ключ одной таблицы указывает на потенциальный ключ в другой таблице. В следующем примере в таблице orderTable создается внешний ключ, который ссылается на таблицу partTable, определенную ранее.
CREATE TABLE orderTable
(orderId int,
partId int
FOREIGN KEY REFERENCES partTable(partId)
ON DELETE NO ACTION,
quantity int);
Вставить строку со значением внешнего ключа, для которого отсутствует потенциальный ключ с таким значением, нельзя, за исключением строки со значением NULL.
Ограничения столбцов и таблиц
Ограничения могут относиться к столбцам или к таблицам. Ограничение столбца указывается в описании столбца и применяется только к данному столбцу. Ограничения, показанные в предыдущих примерах, являются ограничениями столбцов. Ограничение таблицы объявляется независимо от описаний столбцов и может быть применено к нескольким столбцам таблицы. Ограничения таблиц используются при необходимости включить в ограничение несколько столбцов.
Например, если таблица имеет два или более столбцов в первичном ключе, для включения в ограничение всех столбцов первичного ключа необходимо использовать ограничение таблицы.
CREATE TABLE complexPartTable
(
vendorId int,
vendorPartId int,
name char(30),
weight decimal(6,2),
color char(15) ,
CONSTRAINT [PK_complexPartTable] PRIMARY KEY CLUSTERED
(
vendorId,
vendorPartId
)
)
Применение триггеров
Триггер DML - это поименованный фрагмент T-SQL кода (хранимая процедура), выполняемый автоматически при возникновении событий языка обработки данных (DML) в базе данных. DML-события возникают при выполнении инструкций INSERT, UPDATE или DELETE, изменяющих данные в указанной таблице или представлении. DML-триггеры могут обращаться к другим таблицам и содержать сложные инструкции Transact-SQL. Триггер и инструкция, при выполнении которой он срабатывает, считаются одной транзакцией, которую можно откатить назад внутри триггера. Также при обнаружении серьезной ошибки (например, нехватки пространства на диске) вся транзакция автоматически откатывается назад.
DML-триггеры удобно использовать в следующих случаях:
· Для каскадных изменений в связанных таблицах базы данных; в большинстве случаев эти изменения можно более эффективно выполнить при помощи каскадных ограничений ссылочной целостности.
· Для предотвращения случайных или неправильных операций INSERT, UPDATE и DELETE и реализации других более сложных ограничений, чем те, которые определены при помощи ограничения CHECK.
В отличие от ограничений CHECK, DML-триггеры могут ссылаться на столбцы других таблиц. Например, триггер может использовать инструкцию SELECT для сравнения вставленных или обновленных данных и выполнения других действий, например изменения данных или отображения пользовательского сообщения об ошибке.
· Чтобы оценить состояние таблицы до и после изменения данных и предпринять действия на основе этого различия.
Несколько DML-триггеров одинакового типа (INSERT, UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.
Типы триггеров DML
Триггеры AFTER
Триггеры AFTER выполняются после выполнения инструкций INSERT, UPDATE или DELETE. Предложение AFTER по смыслу эквивалентно предложению FOR, которое является единственно доступным выбором для пользователей более ранних версий Microsoft SQL Server. Триггеры AFTER могут быть определены только в таблицах.
Триггеры INSTEAD OF
Триггеры INSTEAD OF выполняются вместо обычных действий INSERT, UPDATE или DELETE. Триггеры INSTEAD OF также могут быть определены в представлениях с одной или более базовыми таблицами, где они могут расширить типы поддерживаемых представлением обновлений.
Рекурсивные триггеры
При выполнении триггером действия, инициирующего другой триггер, триггеры становятся вложенными. Эти действия могут инициировать другие триггеры и т.д. Вложенность триггеров может составлять до 32 уровней. Можно разрешать или запрещать вложенность триггеров AFTER с помощью параметра конфигурации сервера nested triggers.Вложенность триггеров INSTEAD OF не зависит от этого параметра.
Если вложенные триггеры разрешены, и триггер в цепочке начинает бесконечный цикл, это превышает предел уровней вложенности и триггер завершается.
Так как триггеры исполняются в пределах транзакции, сбой на любом уровне набора вложенных триггеров приведет к отмене всей транзакции, а также будет выполнен откат всех изменений данных. Чтобы определить, где именно происходит сбой, удобно использовать в триггерах инструкцию PRINT, выводящую дополнительные данные о последовательности выполнения триггеров.
Триггер AFTER не вызывает самого себя рекурсивно, если только не установлен параметр базы данных RECURSIVE_TRIGGERS.
Существует два типа рекурсии:
· Прямая рекурсия
Такая рекурсия происходит, когда триггер срабатывает и выполняет действие, вызывающее повторное срабатывание того же триггера. Например, приложение обновляет таблицу T3; это вызывает срабатывание триггера Trig3. Триггер Trig3 снова обновляет таблицу T3, при этом триггер Trig3 срабатывает еще раз.
В SQL Server 2008 прямая рекурсия может также возникать, когда повторно вызывается тот же триггер, но лишь после того, как вызван триггер другого типа (AFTER или INSTEAD OF). Другими словами, прямая рекурсия триггера INSTEAD OF может возникать, когда один и тот же триггер INSTEAD OF вызывается второй раз, даже если между двумя его вызовами вызывается один или несколько триггеров AFTER. Аналогичным образом прямая рекурсия триггера AFTER может возникать, когда один и тот же триггер AFTER вызывается второй раз, даже если между двумя его вызовами вызывается один или несколько триггеров INSTEAD OF. Например, пусть приложение использует таблицу T4. Данное обновление приводит к срабатыванию триггера Trig4 типа INSTEAD OF. Trig4 обновляет таблицу T5. Данное обновление приводит к срабатыванию триггера Trig5 типа AFTER. Trig5 обновляет таблицу T4, и это обновление приводит к повторному срабатыванию триггера Trig4 типа INSTEAD OF. Данная цепь событий считается прямой рекурсией триггера Trig4.
· Косвенная рекурсия
Косвенная рекурсия возникает, когда триггер срабатывает и выполняет действие, которое вызывает срабатывание другого триггера того же типа (AFTER или INSTEAD OF). Второй триггер выполняет действие, вызывающее повторное срабатывание исходного триггера. Другими словами, косвенная рекурсия может возникать, когда триггер INSTEAD OF вызывается второй раз, но лишь после того, как между этими двумя вызовами вызывается другой триггер того же типа INSTEAD OF. Аналогичным образом косвенная рекурсия может возникать, когда триггер AFTER вызывается второй раз, но лишь после того, как между этими двумя вызовами вызывается другой триггер того же типа AFTER. Например, пусть приложение использует таблицу T1. Данное обновление приводит к срабатыванию триггера Trig1 типа AFTER. Триггер Trig1 обновляет таблицу T2; при этом обновлении срабатывает триггер Trig2 типа AFTER. Триггер Trig2, в свою очередь, обновляет таблицу T1, что приводит к повторному срабатыванию триггера Trig1 типа AFTER.
Когда для параметра базы данных RECURSIVE_TRIGGERS устанавливается значение OFF, предотвращается только прямая рекурсия триггеров AFTER. Чтобы отключить косвенную рекурсию триггеров AFTER, необходимо присвоить параметру сервера nested triggers значение 0.