Для программ, работающих с БД, важным является не только корректность обработки ошибок БД, но и формирование информативных сообщений об этих ошибках. Наличие таких сообщений позволяет быстрее выявлять причины ошибок изменения данных БД и их исправлять. Особенно это актуально для пользователей программных продуктов, которым в большинстве случаев может быть не известна не только структура конкретной БД, но и теоретические основы реляционных баз данных. Хотя Microsoft SQL Server и формирует довольно подробные сообщения об ошибках ограничений базы данных, но при непосредственной передаче их пользователям в большинстве случаев, сложно рассчитывать на то, что ему удастся выявить по такому сообщению причину ошибки. Можно выделить несколько основных причин возникновения такой ситуации:
- Использование в клиентских программах обозначений таблиц и полей (пользовательских названий), отличных от их имен в базе данных. Например, таблица базы данных имеет имя "Goods", а в клиентском приложении данные этой таблицы могут отображаться в справочнике с названием "Товары" или "Продукция". Соответственно если возникнет ошибка, вызванная ограничением этой таблицы, то сервер сформирует сообщение, в котором будет фигурировать название таблицы "Goods", а не пользовательское название таблицы, которое известно пользователю.
- Отсутствие у пользователей достаточных знаний о структуре базы данных и особенностях хранения данных в ней. Согласитесь, что довольно абсурдно требовать от кассира теоретических знаний о базах данных только для того, чтобы он смог принять платёж от клиента.
- Зависимость сообщения об ошибке от назначения программы. Даже для программ, работающих с одной и той же БД, может потребоваться формирование различных сообщений об одной и той же ошибке. Например, в программе для редактирования данных БД пользователем сообщение должно быть: "Товар с таким названием уже зарегистрирован ! Проверьте название товара ! ". А в программе импорта данных может потребоваться сообщение об этой же ошибке но с совершенно другим содержанием: "Импортируемые данные дублируются - проверьте данные об импортируемых товарах ! ".
- При формировании сообщений об ошибках сервер баз данных не учитывает логической структуры базы данных. Например, ограничения внешних ключей могут использоваться для реализации различных логических связей между таблицами: один к одному, один ко многим, многие ко многим. Для формирования понятного для пользователя сообщения об ошибке для каждой из этих ситуаций должны формироваться отдельные сообщения, иначе они не только не помогут пользователю выявить причину ошибки, но и могут еще больше его запутать.
- Сложность формирования сообщений для некоторых ошибок, вызванных ограничениями БД. Например, в ограничениях CHECK для таблиц могут использоваться довольно сложные запросы и условия. Поэтому формирование сообщений на основе их анализа может оказаться довольно сложной задачей.
Совокупность этих факторов обычно приводит к тому, что формирование сообщений даже об однотипных ошибках реализуется индивидуально для каждой транзакции. В результате код для формирования сообщений об ошибках оказывается распределенным по всему приложению, что усложняет его сопровождение. Из-за необходимости написания кода практически для каждой возможной ошибки, часть ошибок, даже о которых разработчику известно, оказываются без соответствующих сообщений для пользователя. В результате достаточно информативные сообщения формируются только для некоторой части ошибок, в остальных же случаях пользователю остается довольствоваться сообщениями от самого сервера баз данных. Как уже было рассмотрено выше, информативность таких сообщений в большинстве случаев недостаточна для выявления причины возникшей проблемы и её устранения.
Это приводит к тому, что формирование сообщений об ошибках БД довольно часто сильно отличается от обработки самих ошибок. При обработке ошибок БД обычно удается выработать общую стратегию, что позволяет локализовать их обработку в одном или нескольких местах программы. Для формирования сообщений об ошибках БД можно применить такой же подход, если сообщения об ошибках условно разделить на две группы:
- универсальные сообщения, которые формируются на основе анализа структуры БД;
- специальные сообщения, которые определяются индивидуально для каждой ошибки.
Это позволит локализовать формирование сообщений об ошибках в одном месте программы и упростить общую схему обработки ошибок БД.
Универсальные и специальные сообщения об ошибках
Универсальные сообщения об ошибках
Формирование сообщений этого типа основано на том, что в тексте ошибки Microsoft SQL Server указывает информацию об ошибке и объекте БД, который явился причиной её возникновения. Такими объектами обычно являются ограничения БД, такие как, главные, уникальные и внешние ключи, уникальные индексы, ограничения "not null" и др. На основе этих сообщений могут быть в ряде случаев сформированы понятные для пользователя сообщения об ошибках. Для формирования таких сообщений необходима информация о логической структуре базы данных, которая может быть получена из системного каталога БД. Информация о пользовательских названиях таблиц и их полей может хранится в БД и использоваться при формировании сообщений. Для этого, например, могут использоваться расширенные свойства таблиц и их полей.
Специальные сообщения об ошибках
Необходимость использования специальных сообщений может возникнуть в случае, если универсальное сообщение об ошибке по каким-то причинам не отражает реальную причину ошибки или не может быть сформировано. Примером последнего случая являются ограничения CHECK для таблиц. В условиях ограничений могут использоваться довольно сложные запросы и условия, анализ которых может оказаться довольно сложной задачей. Поэтому для этих ограничений часто удобнее использовать сообщения, которые определяются на этапе разработки.
Можно выделить две группы специальных сообщений об ошибках БД:
- Первая группа сообщений предназначена для использования во всех приложениях, которые работают c общей базой данных. Их можно условно назвать "специальные сообщения уровня базы данных".
- Вторая группа сообщений специфична для конкретного приложения. Они могут быть необходимы, когда различные приложения должны выдавать пользователю различные сообщения об одной и той же ошибке. Их можно условно назвать "специальные сообщения уровня приложения".
Информация о первой группе сообщений может храниться в БД. Вторая группа сообщений может храниться как в приложении (например, в виде ресурсов или в специальном файле), так и в БД, но с указанием приложения для которого она предназначена. При выборе варианта хранения специальных сообщений в ряде случаев имеет смысл учитывать, что может оказаться удобной возможность корректировки таких сообщений без перекомпиляции приложения. Это может, например, потребоваться если разработкой занимается одна группа сотрудников, а поддержкой - другая группа. В этом случае группа поддержки может самостоятельно дополнять или изменять специальные сообщения об ошибках.
Хотя до сих речь шла только о текстовых сообщениях, но необходимо принимать во внимание, что специальные сообщения могут быть, например, в формате HTML или RTF, содержать не только текст, но и изображения, ссылки на разделы документации или справочной системы.
Специальные сообщения в базе данных могут храниться, например, в отдельной таблице или как расширенные свойства ограничений БД. Последний вариант может оказаться в ряде случаев более предпочтительным, чем использование для этого отдельной таблицы. При изменении имени ограничения или его удалении не потребуется дополнительно проводить изменения в отдельной таблице. Расширенные свойства ограничения будут удалены при удалении ограничения, а изменение имени ограничения не потребует корректировки расширенных свойств (если не требуется изменение самого специального сообщения).
Комплексное использование специальных и универсальных сообщений об ошибках
Универсальные и специальные сообщения об ошибках позволяют реализовать довольно гибкую схему формирования сообщений об ошибках БД для конечного пользователя. Как уже было описано выше, для каждой ошибки может использоваться несколько сообщений:
- специальное сообщение уровня приложения;
- специальное сообщение уровня БД;
- универсальное сообщение, формируемое на основе анализа структуры БД;
- исходное сообщение Microsoft SQL Server.
Типы сообщений перечислены в порядке приоритета их выбора при возникновении ошибки (рис. 1).
Рис. 1. Последовательность формирования сообщения об ошибке базы данных.
Если для ошибки не задано специальное сообщение (уровня БД или уровня приложения), то будет выводиться универсальное сообщение об ошибке, которое формируется на основе анализа структуры БД. Если по каким-то причинам оно не может быть сформировано, то выводится исходное сообщение от Microsoft SQL Server.
Если для ошибки определено специальное сообщение, то будет выводиться именно это сообщение. Причём специальное сообщение уровня приложения имеет в этой схеме "больший приоритет", чем специальное сообщение уровня БД.
Описанная последовательность формирования универсальных и специальных сообщений об ошибках ориентирована на то, что для большинства ошибок могут быть сформированы универсальные сообщения на основе анализа физической и логической структуры БД. Для тех же ошибок, для которых эти сообщения недостаточно информативны, или для случаев, когда отдельные приложения требуют особых формулировок сообщений об ошибках, могут использоваться специальные сообщения уровня БД и уровня приложения.
Если специальные сообщения хранятся в БД, то эта схема может позволить расширить возможности группы технической поддержки программы (или программного комплекса), давая ей возможность создавать специальные сообщения (уровня БД и уровня приложения) для тех ошибок БД, для которых она считает это необходимым.
Формирование универсальных сообщений
Как уже говорилось выше, основная идея использования универсальных сообщений заключается в том, что они могут быть сформированы автоматически на основе данных об ошибке от Microsoft SQL Server и о структуре БД.
Предположим, в таблицу "Goods" (скрипт 1) пользователь пытается добавить товар с названием (поле "Title"), которое уже имеется в таблице.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Goods](
[Code] [int] NOT NULL,
[Title] [varchar](50) NOT NULL,
[Price] [numeric](16, 2) NOT NULL,
CONSTRAINT [PK_Goods] PRIMARY KEY CLUSTERED ([Code] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_Goods_Title] UNIQUE NONCLUSTERED ([Title] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Код товара',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Goods', @level2type=N'COLUMN', @level2name=N'Code'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Название',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Goods', @level2type=N'COLUMN', @level2name=N'Title'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Цена',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Goods', @level2type=N'COLUMN', @level2name=N'Price'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Товары',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Goods';
ALTER TABLE [dbo].[Goods] WITH CHECK ADD CONSTRAINT [CK_Price] CHECK (([Price]>=(0)))
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Цена товара не может быть отрицательной' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Goods',
@level2type=N'CONSTRAINT', @level2name=N'CK_Price'
GO
Скрипт 1. Создание таблицы "Goods".
Сервер в этом случае генерирует ошибку, так как поле "Title", в котором хранится название товара, включено в уникальный индекс "IX_Goods_Title":
Вместо этого сообщения для пользователя может быть сформировано, например, одно из сообщений:
- Значение поля "Название" таблицы "Товары" должно быть уникальным !
- Товар с таким названием уже зарегистрирован ! Проверьте название товара !
- В справочнике товаров не могут быть товары с одинаковыми названиями !
Хотя эти сообщения и различаются, но во всех сообщениях указывается информация об объекте БД, для которого нарушено ограничение уникальности - это поле "Название" таблицы "Товары".
Как уже обсуждалось выше, одна из проблем формирования такого типа сообщений, заключается в том, что пользовательские названия полей и таблиц (названия таблиц и полей в клиентских программах), отличаются от их имен в БД. Чтобы пользователю было понятно сообщение об ошибке, в сообщении должны использоваться именно пользовательские названия полей и таблиц. Для сопоставления имен таблиц и полей и их пользовательских названий может использоваться отдельная таблица БД или дополнительные свойства (extended property) таблиц и полей. Последний вариант является более предпочтительным, так как это позволяет одновременно документировать БД и избежать корректировки дополнительной таблицы при изменении структуры БД.
Microsoft SQL Server Manager Studio для многих объектов БД в списке их основных свойств отображает дополнительное свойство "MS_Description" под названием "Description" (рис. 2).
Рис. 2. Отображение свойства "Description" для таблицы в Microsoft SQL Server Manager Studio.
Другие дополнительные свойства Microsoft SQL Server Manager Studio отображет на вкладке "Extended Properties" (рис. 3).
Рис. 3. Отображение расширенных свойств таблицы в Microsoft SQL Server Manager Studio.
Для добавления, изменения и удаления расширенных свойств объектов используются системные процедуры sp_addextendedproperty, sp_dropextendedproperty, sp_updateextendedproperty. Для просмотра расширенных свойств объектов могут использоваться системная функция fn_listextendedproperty и системное представление sys.extended_properties.
В скрипте 1 пользовательские названия таблицы и её полей сохранены как значения дополнительного свойства "MS_Description".
Если сравнить выше приведённые сообщения и значение дополнительного свойства "MS_Description" для таблицы и полей, то можно заметить, что формирование первого сообщения является наиболее простым вариантом. Для формирования двух других сообщений может потребоваться лексический синтез, но это уже отдельная тема. Хочется обратить внимание, что в дальнейшем в статье приводится только один из возможных вариантов сообщения для каждого случая ошибки. На практике выбор стиля сообщения об ошибке и его содержания может зависеть от целого ряда факторов, и будет определяться разработчиком системы.
Конечно, нельзя исключать ситуацию, когда информация о пользовательском названии таблицы или поля, которые должны быть указаны в сообщении, отсутствуют. В этой ситуации в сообщении об ошибке придется использовать непосредственно имя таблицы или поля.
Для баз данных Microsoft SQL Server можно выделить ряд наиболее часто встречающихся ошибок, обусловленных ограничениями БД:
- не указано значение поля, обязательное для заполнения (ограничение NOT NULL);
- нарушена уникальность поля или набора полей;
- нарушена ссылочная целостность таблиц;
- нарушено ограничение CHECK для таблицы.
Среди этих причин наибольшую сложность в интерпретации вызывают ограничения внешних ключей. Обусловлено это тем, что они используются для реализации различных логических связей между таблицами. Это так же усложняет анализ и некоторых других ограничений, так как в ряде случаев они так же используются для реализации этих логических связей.
Поэтому далее сначала рассматривается относительно простые случаи формирования универсальных сообщений, а затем рассматривается создание сообщений для ограничений внешних ключей и их взаимосвязь с логическими связями между таблицами.
1. Не указано значение поля, обязательное для заполнения (ограничение NOT NULL)
При возникновении данной ситуации в результате добавления или редактирования записи сервер генерирует ошибку с кодом 515 и текстом:
Для операций вставки и изменения записи эти сообщения об ошибке различаются названием операции, при выполнении которой произошла ошибка. Для отражения этого момента в выше приведенных сообщениях используется обозначение
"<insert/updata>"
для указания того, что в зависимости от ситуации в сообщении будет присутствовать одно из слов, указанных через разделитель "/" .
Для получения пользовательского названия таблицы, к которой относится поле, можно использовать запрос 2.
FROM fn_listextendedproperty ('MS_Description', 'schema', '<Схема>',
'table', "<Таблица>", NULL, NULL)
Запрос 2. Получение пользовательского названия таблицы.
В качестве параметров запроса <Схема> и <Таблица> необходимо указать соответственно имя схемы и таблицы, из сообщения об ошибке.
Для получения пользовательского названия поля таблицы можно использовать запрос 3.
FROM fn_listextendedproperty ('MS_Description', 'schema', '<Схема>',
'table', "<Таблица>", 'COLUMN', "<Поле>")
Запрос 3. Получение пользовательского названия поля таблицы.
В качестве параметров запроса <Схема>, <Таблица>, <Поле> необходимо соответственно указать имена схемы, таблицы и поля таблицы из сообщения об ошибке.
Используя результаты этих запросов можно сформировать сообщение, например, следующего содержания:
Конечно, содержание сообщения может варьироваться в зависимости от типа программы и стиля общения программы с пользователем. Например, в программе таблицы могут называться справочниками. Если изменение выполняется пользователем в интерактивном режиме, то, скорее всего, не потребуется дополнительно уточнять, при выполнении какой именно операции произошла ошибка: при добавлении записи (операция INSERT) или при её изменении (операция UPDATE). Но если ошибка произошла при импорте данных в БД, то информации об операции может оказаться довольно актуальной.
В качестве примера можно рассмотреть попытку добавления записи в таблицу "Goods" (скрипт 1.1) со значением NULL для строкового поля "Title":
Сервер в этом случае генерирует сообщение об ошибке:
Для получения пользовательских названий таблицы "Goods" и её поля "Title" можно использовать запросы 2 и 3. В результате выполнения запроса 2 будет получено пользовательское название таблицы "Goods" - "Товары", а в результате выполнения запроса 3 пользовательское название её поля "Title" - "Название". Используя полученные пользовательские названия таблицы и поля можно сформировать сообщение об ошибке:
2. Нарушена уникальность значения поля или набора полей
Необходимость ввода уникального значения поля может требоваться в основном в трех случаях:
- поле входит в главный ключ;
- поле включено в уникальный ключ;
- поле входит в уникальный индекс.
В первых двух случаях Microsoft SQL Server генерирует один и тот же код ошибки 2627, но разные сообщения. В первом случае текст ошибки имеет содержание:
Во втором случае в тексте ошибки указывается, что причиной является уникальный ключ:
При нарушении уникального индекса Microsoft SQL Server генерирует ошибку с кодом о 2601 и текстом:
Как можно заметить, в этих сообщениях не указывается поле или набор уникальных полей, которые явились причиной ошибки. Эту информацию можно получить на основе информации о ключе или индексе.
Для получения информации о полях, входящих в индекс, главный или уникальный ключ, можно использовать запрос 4.
epi.value AS [Description],
t.name AS [Table Name],
c.name AS [Field Name],
ept.value AS [Table Description],
ep.value AS [Field Description]
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id
AND c.column_id = ic.column_id
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
LEFT JOIN sys.extended_properties AS ep ON ep.minor_id = c.column_id
AND ep.major_id = t.object_id AND ep.Name = 'MS_Description'
LEFT JOIN sys.extended_properties AS ept ON ept.minor_id = 0
AND ept.major_id = t.object_id AND ept.Name = 'MS_Description'
LEFT JOIN sys.extended_properties AS epi ON epi.minor_id = i.index_id
AND epi.major_id = i.object_id AND epi.class = 7 AND epi.Name = 'MS_Description'
WHERE (i.name = '<Ключ или индекс>')
Запрос 4. Получение информации о полях таблицы, входящих в индекс, главный или уникальный ключ.
В качестве результата запрос возвращает: "Name" и "Index Description" - имя и описание ключа или индекса; "Table Name", "Table Description" - имя и описание таблицы; "Field Name" и "Field Description" - имя поля и его описание. После получения этой информации для пользователя может быть сформировано сообщение, например, следующего содержания:
Если в ключ или индекс входит несколько полей, то запрос вернет несколько записей, количество которых будет соответствовать количеству полей в ключе или индексе. Значения полей "Name", "Description", "Table Name", "Table Description" и "Index Description" будут в записях повторяться, а вот значения полей "Field Name" и "Field Description" будут различными и содержать информацию о каждом из полей. Сообщение в этом случае может иметь, например, следующий вид:
В частности, для примера нарушения ограничения уникального индекса "IX_Goods_Title", который рассматривается в начале статьи, результат выполнения запроса получения информации об этом индексе представлен в таблице 1.
| Name | Description | Table Name | Field Name | Table Description | Field Description |
| IX_Goods_Title | NULL | Goods | Title | Товары | Название |
Таблица 1. Результат выполнения запроса 4 со значением 'IX_Goods_Title' параметра <Ключ или индекс>.
Как видно на основе данных о полях таблицы могут быть сформированы различные варианты сообщений о причине ошибке, и в частности те, которые приведены в начале статьи.
3. Ограничения внешних ключей
Сложность формирования сообщений об ошибках на основе анализа структуры базы данных (универсальных сообщений), вызванных ограничениями внешних ключей, связана в первую очередь тем, что внешние ключи используются для реализации различных логических связей между таблицами. Поэтому для формирования информативного сообщения кроме получения информации о внешнем ключе, вызвавшем ошибку, требуется так же дополнительный анализ связей таблиц внешнего ключа с другими таблицами для определения логической связи между ними.
Можно выделить несколько причин, приводящих к нарушению ограничений внешних ключей:
- В подчинённую таблицу добавляется запись, в которой для поля внешнего ключа нет соответствующего значения в главной таблице. Аналогичная ситуация происходит при изменении значения поля подчиненной таблицы в случае, если нового значения поля нет в главной таблице.
- Во внешней таблице выполняется попытка удаления данных, на которые имеется ссылка в подчинённой таблице. При этом в определении связи между таблицами указано ограничение "NO ACTION" для операции удаления данных. При такой связи Microsoft SQL Server не позволяет удалять данные из внешней таблицы, если в подчинённой таблице есть записи связанные с удаляемой записью.
- Ситуация аналогичная п.2, но только для операции изменения данных во внешней таблице с ограничением "NO ACTION". Microsoft SQL Server в этом случае генерирует те же ошибки, что и при удалении данных.
При добавлении или изменение значения поля в подчинённой таблице, для которого нет соответствующего значения во внешней таблице Microsoft SQL Server генерирует ошибку с кодом 547 и текстом:
Если во внешний ключ входит всего одно поле, то сообщение об ошибке имеет более подробное содержание:
Для получения информации о полях внешней и подчинённой таблиц, входящих во внешний ключ, можно использовать приведенный ниже запрос 5.
OBJECT_NAME (f.parent_object_id) AS [Table],
ept.value AS [Table description],
COL_NAME (fc.parent_object_id, fc.parent_column_id) AS [Column],
epc.value AS [Column description],
OBJECT_NAME (f.referenced_object_id) AS [Referenced table],
ept_ref.value AS [Referenced table description],
COL_NAME (fc.referenced_object_id, fc.referenced_column_id) AS [Referenced Column],
epc_ref.value AS [Referenced Column description],
ix.name AS [Unique referenced table],
[Unique type referenced table] =
CASE
WHEN ix.is_primary_key = 1 THEN 'Primary key'
WHEN ix.is_unique_constraint = 1 THEN 'Unique constraint'
WHEN ix.is_unique = 1 THEN 'Unique index'
END,
delete_referential_action_desc AS [Delete referential action],
update_referential_action_desc AS [Update referential action]
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
INNER JOIN sys.indexes as ix ON (f.referenced_object_id = ix.object_id)
AND (f.key_index_id = ix.index_id)
LEFT JOIN sys.extended_properties AS epc ON epc.minor_id = fc.parent_column_id
AND epc.major_id = fc.parent_object_id AND epc.Name = 'MS_Description'
LEFT JOIN sys.extended_properties AS ept ON ept.minor_id = 0
AND ept.major_id = fc.parent_object_id AND ept.Name = 'MS_Description'
LEFT JOIN sys.extended_properties AS epc_ref
ON epc_ref.minor_id = fc.referenced_column_id
AND epc_ref.major_id = fc.referenced_object_id AND epc_ref.Name = 'MS_Description'
LEFT JOIN sys.extended_properties AS ept_ref ON ept_ref.minor_id = 0
AND ept_ref.major_id = f.referenced_object_id AND ept_ref.Name = 'MS_Description'
WHERE f.name = '<Внешний ключ>';
Запрос 5. Получение информации о внешнем ключе <Внешний ключ>.
В качестве параметра запроса <Внешний ключ> необходимо указать название внешнего ключа из сообщения об ошибке. Поля в результирующем запросе имеют следующее значение:
- 'Table', 'Table description' - имя и описание ссылочной таблицы;
- 'Column', 'Column description' - имя и описание поля ссылочной таблицы, входящего во внешний ключ;
- 'Referenced table', 'Referenced table description' - имя и описание внешней таблицы;
- 'Unique referenced table', 'Unique type referenced table' - имя и тип уникального ограничения внешней таблицы, используемого для создания внешнего ключа;
- 'Referenced Column', 'Referenced Column Description' - имя и описание поля внешней таблицы;
- 'Delete referential action', 'Update referential action' - правила удаления и обновления для внешнего ключа.
Таким образом, можно получить информацию о полях, входящих во внешний ключ со стороны внешней и ссылочной таблиц. Это дает возможность сформировать сообщение, довольно точно отражающее причину ошибки, например, следующего содержания:
В случае удаления или изменения поля внешней таблицы, входящего во внешний ключ с правилом обновления "NO ACTION", и если в ссылочной таблице имеются записи, ссылающиеся на изменяемую запись, то Microsoft SQL Server сгенерирует ошибку с кодом 547 и текстом:
Если во внешний ключ входит всего одно поле, то в тексте ошибки дополнительно будет указано название поля внешней таблицы, используемое во внешнем ключе:
Для определения взаимосвязи между внешней и ссылочной таблицами можно использовать запрос 5.
Если связь между таблицами осуществляется по одному полю, то сообщение для пользователя может иметь, например, следующее содержание:
Если же связь между таблицами осуществляется по нескольким полям, то сообщение может иметь, например, следующее содержание:
Аналогичная ситуация происходит, если ограничение "NO ACTION" установлено для операции изменения данных. Microsoft SQL Server в этом случае генерирует ошибку с тем же кодом и текстом, в котором указывается, что ошибка произошла при выполнении операции изменения данных:
Если во внешний ключ входит всего одно поле, то в сообщении об ошибке будет дополнительно указано название поля внешней таблицы, используемое во внешнем ключе:
В качестве примера можно рассмотреть ошибки внешних ключей для таблиц "Goods" и "Sales" (скрипт 6) связанных с помощью внешнего ключа "FK_Sales_Goods".
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sales](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Goods] [int] NOT NULL,
[Qty] [numeric](15, 3) NOT NULL,
[Discount] [numeric](16, 2) NULL CONSTRAINT [DF_Sales_Discount] DEFAULT ((0)),
[Summ] [numeric](16, 2) NOT NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
([ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Товар',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Sales', @level2type=N'COLUMN', @level2name=N'Goods'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Цена',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Sales', @level2type=N'COLUMN', @level2name=N'Qty'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Скидка',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Sales', @level2type=N'COLUMN', @level2name=N'Discount'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Сумма',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Sales', @level2type=N'COLUMN', @level2name=N'Summ'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Продажи',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Sales'
GO
ALTER TABLE [dbo].[Sales] WITH CHECK
ADD CONSTRAINT [FK_Sales_Goods] FOREIGN KEY([Goods]) REFERENCES [dbo].[Goods] ([Code])
GO
Скрипт 6. Создание таблицы "Sales".
Если в таблицу "Sales" попробовать добавить запись, в которой для поля "Goods" нет соответствующего значения в таблице "Goods", то сервер сгенерирует сообщение об ошибке с кодом 547:
Если выполнить запрос 5 с названием внешнего ключа 'FK_Sales_Goods' из сообщения об ошибке в качестве значения параметра <Внешний ключ>, то можно получить результат, представленный в таблице 2.
Таблица 2. Результат выполнения запроса 5 для внешнего ключа "FK_Sales_Goods".
| Table | Table descrip-tion | Column | Column description | Referenced table | Referenced table description |
| Sales | Продажи | Goods | Товар | Goods | Товары |
продолжение Таблицы 2.
| Referenced Column | Referenced Column description | Unique referenced table | Unique type referenced table | Delete referential action |
| Code | Код товара | PK_Goods | Primary key | NO_ACTION |
На основе этих данных может быть сформировано сообщение для пользователя, например, следующего содержания:
При попытке удаления записи из таблицы "Goods", которая является внешней для таблицы "Sales" Microsoft SQL Server сгенерирует ошибку с текстом:
После анализа структуры БД с помощью запроса 5 (табл. 2) можно сформировать сообщение об ошибке:
4. Внешние ключи и логические связи между таблицами
Для формирования информативных сообщений об ошибках внешних ключей необходимо наличие информации о логических связях между таблицами для реализации которых они используются.
Примером может являться использование внешнего ключа для ссылки на собственную таблицу. Понятно, что в этом случае сообщение об ошибке должно отличаться от случая, когда главная и подчинённая таблицы являются различными.
Другим примером является формирование сообщений для таблиц с логической связью "многие ко многим". Обычно такая связь между таблицами реализуется с помощью дополнительной таблицы, и внешние ключи непосредственно не связывают таблицы между которыми реализуется логическая связь.
Поэтому если использовать сообщения об ошибке, сформированные на основе только информации об ограничениях между таблицами, то они, скорее всего, не смогут адекватно отразить причину ошибки.
4.1. Сообщения об ошибках для таблиц с логической связью "многие ко многим"
Обычно такая связь реализуется с помощью дополнительной таблицы. В дополнительной таблице создаются внешние ключи, которые ссылаются на таблицы, между которыми и реализуется связь "многие ко многим". Чтобы избежать дублирования связей между таблицами, в дополнительной таблице на поля, которые входят в эти внешние ключи, накладывается ограничение уникальности (уникальный индекс, первичный или уникальный ключ). Оба внешних ключа дополнительной таблицы ссылаются на поля таблиц логической связи "многие ко многим", которые входят в их первичные ключи. Для создания новой связи между записями логически связанных таблиц в дополнительную таблицу добавляется новая запись, а для удаления имеющейся связи между записями таблиц с логической связью "многие ко многим" удаляется соответствующая запись из дополнительной таблицы, т.е. все изменения выполняются только в дополнительной таблице, правильность которых контролируется ограничениями этой таблицы.
Если произошла ошибка, вызванная ограничением внешнего ключа, то для выявления логической связи "многие ко многим" можно использовать её характерные признаки:
- Внешний ключ, ограничение которого вызвало ошибку, входит также в ограничение уникальности (уникальный индекс, первичный или уникальный ключ). Это соответствует структуре дополнительной таблицы, используемой для реализации логической связи "многие ко многим".
- В это ограничение уникальности дополнительной таблицы входят поля другого внешнего ключа. Другие поля в это ограничение уникальности дополнительной таблицы не входят. Таблицы, на которые ссылаются эти внешние ключи, как раз и связаны логической связью "многие ко многим".
В качестве примера такой логической связи можно рассмотреть взаимосвязь между таблицами "Goods" (скрипт 1) и "Provider" (скрипт 7), в качестве связующей используется таблица "GoodsProvider" (скрипт 8).
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Provider](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Address] [varchar](50) NULL,
[IDMain] [int] NULL,
CONSTRAINT [PK_Provider] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_Provider] UNIQUE NONCLUSTERED ( [Name] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Поставщик',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Provider',
@level2type=N'COLUMN', @level2name=N'ID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Название',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Provider', @level2type=N'COLUMN', @level2name=N'Name'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Адрес',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Provider', @level2type=N'COLUMN', @level2name=N'Address'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Главный поставщик',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Provider',
@level2type=N'COLUMN', @level2name=N'IDMain'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Поставщики',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Provider'
GO
ALTER TABLE [dbo].[Provider] WITH CHECK ADD CONSTRAINT [FK_Provider_Provider]
FOREIGN KEY([IDMain]) REFERENCES [dbo].[Provider] ([ID])
GO
Скрипт 7. Создание таблицы "Provider".
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GoodsProvider](
[GoodsID] [int] NOT NULL,
[ProviderID] [int] NOT NULL,
[Price] [numeric](16, 2) NOT NULL,
CONSTRAINT [PK_GoodsProvider] PRIMARY KEY CLUSTERED(
[GoodsID] ASC,
[ProviderID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Товар',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'GoodsProvider', @level2type=N'COLUMN', @level2name=N'GoodsID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Поставщик',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'GoodsProvider', @level2type=N'COLUMN', @level2name=N'ProviderID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Цена',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'GoodsProvider', @level2type=N'COLUMN', @level2name=N'Price'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Товары и поставщики',
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'GoodsProvider'
GO
ALTER TABLE [dbo].[GoodsProvider] WITH CHECK
ADD CONSTRAINT [FK_GoodsProvider_Goods] FOREIGN KEY([GoodsID])
REFERENCES [dbo].[Goods] ([Code])
ALTER TABLE [dbo].[GoodsProvider] WITH CHECK
ADD CONSTRAINT [FK_GoodsProvider_Provider]
FOREIGN KEY([ProviderID]) REFERENCES [dbo].[Provider] ([ID])
GO
Скрипт 8. Создание таблицы "GoodsProvider".
Поля "Goods" и "Provider" таблицы "GoodsProvider" входят в состав внешних ключей, которые ссылаются на таблицы "Goods" и "Provider", между которыми и реализуется логическая связь "многие ко многим". Во внешние ключи "FK_GoodsProvider_Provider" и "FK_GoodsProvider_Goods" таблицы "GoodsProvider" входят поля составляющие первичные ключи таблиц "Goods" и "Ppoviders", между которыми реализуется логическая связь "многие ко многим". Чтобы избежать дублирования взаимосвязи между записями таблиц "Goods" и "Providers" в таблице "GoodsProvider" используется первичный ключ "PK_GoodsProvider", в который входят поля обоих внешних ключей "FK_GoodsProvider_Provider" и "FK_GoodsProvider_Goods".
При необходимости реализовать связь между записями таблиц "Goods" и "Providers" добавляется запись в дополнительную таблицу "GoodsProvider", в которой в качестве значений полей "GoodsID" и "ProviderID" указываются значения полей "Goods.Code" и "Providers.ID", составляющих первичные ключи таблиц "Goods" и "Providers". При необходимости разорвать связь между записями таблиц "Goods" и "Providers" в дополнительной таблице "GoodsProvider" удаляется запись, в которой значения её полей "GP_GOODS" и "GP_PROVIDER" совпадают со значениями полей таблиц "Goods.Code" и "Provider.ID".
Для таблиц, участвующих в логической связи "многие ко многим", можно выделить несколько ситуаций, которые могут приводить к возникновению ошибок.
1. Делается попытка создания связи для несуществующей записи одной из таблиц, участвующих в логической связи "многие ко многим"
Это соответствует ситуации, когда выполняется попытка добавления в промежуточную таблицу записи, значения полей которой нарушают ограничение одного из внешних ключей. Например, если попробовать добавить в таблицу GoodsProvider запись, сcылающуюся на несуществующее значение поле ID таблицы Provider:
То это приведет к ошибке 574 с текстом:
Для формирования сообщения об ошибке сначала необходимо определить наличие логической связи "многие ко многим" между таблицами, т.к. в общем случае о наличии логических связей между таблицами может быть ничего не известно. Для получения информации о внешнем ключе, вызвавшем ошибку, можно использовать запрос 5. Результат запроса 5 для внешнего ключа "FK_GoodsProvider_Provider" из сообщения об ошибке представлен в таблице 3.
Таблица 3. Результат выполнения запроса 5 для внешнего ключа "FK_GoodsProvider_Provider".
| Table | Table description | Column | Column description | Referenced table | Referenced table description |
| GoodsProvider | Товары и поставщики | ProviderID | Поставщик | Provider | Поставщики |
продолжение Таблицы 3.
| Referenced Column | Referenced Column description | Unique referenced table | Unique type referenced table | Delete referential action |
| ID | Поставщик | PK_Provider | Primary key | NO_ACTION |
Как было описано выше, поля дополнительной таблицы "GoodsProvider", входящие во внешний ключ "FK_GoodsProvider_Provider", должны так же входить в ограничение уникальности.
Запрос 9 возвращает информацию о внешних ключах, которые связаны ограничением уникальности.
OBJECT_NAME (fk.referenced_object_id) AS [Table],
OBJECT_NAME (ixc.object_id) AS [Supplementary table],
OBJECT_NAME (fk.object_id) AS [Foreign key],
ix.name AS [Unique name],
[Unique type] =
CASE
WHEN ix.is_primary_key = 1 THEN 'Primary key'
WHEN ix.is_unique_constraint = 1 THEN 'Unique constraint'
WHEN ix.is_unique = 1 THEN 'Unique index'
END
FROM sys.foreign_keys fk, sys.foreign_key_columns fkc,
sys.indexes ix, sys.index_columns ixc
WHERE
ix.object_id = (SELECT ix.object_id
FROM sys.foreign_keys fk,sys.foreign_key_columns fkc,
sys.indexes ix, sys.index_columns ixc
WHERE
fk.name = '<Внешний ключ>'
AND (fkc.constraint_object_id = fk.object_id)
AND ix.object_id = fk.parent_object_id
AND ixc.object_id = ix.object_id
AND ixc.column_id = fkc.constraint_column_id
AND (ix.is_primary_key = 1 or ix.is_unique_constraint = 1 OR ix.is_unique = 1)
)
AND (fkc.constraint_object_id = fk.object_id)
AND ix.object_id = fk.parent_object_id
AND ixc.object_id = ix.object_id
AND ixc.column_id = fkc.constraint_column_id
Запрос 9. Внешние ключи, связанные ограничением уникальности.
Поля запроса имеют следующие значения:
- "Table" - одна из таблиц логической связи "многие ко многим";
- "Supplementary table" - дополнительная таблица, которая используется для реализации логической связи "многие ко многим";
- "Foreign key" - имя внешнего ключа дополнительной таблицы, который участвует в реализации связи "многие ко многим";
- "Unique name", "Unique type" - имя и тип ограничения уникальности (уникальный индекс, главный или уникальный ключ), которое связывает поля внешних ключей в дополнительной таблице.
В качестве параметра <Внешний ключ> запроса 9 необходимо указать имя внешнего ключа.
Если ограничение уникальности "объединяет" в несколько внешних ключей, то запрос вернет для этого ограничения (поле запроса "Unique name") количество записей, соответствующее количеству таких внешних ключей (поле запроса "Foreign key").
Если выполнить запрос 9 со значением "FK_GoodsProvider_Provider" параметра <Внешний ключ>, то он вернет результат, представленный в представленный в таблице 4.
| Table | Supplementary table | Foreign key | Unique name |
| Goods | GoodsProvider | FK_GoodsProvider_Goods | PK_GoodsProvider |
| Provider | GoodsProvider | FK_GoodsProvider_Provider | PK_GoodsProvider |
Таблица 4. Результат выполнения запроса 9 для внешнего ключа "FK_GoodsProvider_Provider".
Как видно из таблицы 4, в главный ключ "PK_GoodsProvider" таблицы "GoodsProvider" входят поля внешних ключей "FK_GoodsProvider_Provider" и "FK_GoodsProvider_Goods", которые и реализуют логическую связь "многие ко многим" между таблицами "Provider" и "Goods".
В качестве варианта сообщения об ошибке может использоваться, например:
2. В одной из таблиц логической связи "многие ко многим", выполняется удаление записи, которая связана с записью другой таблицы логической связи
Если для операции удаления внешнего ключа установлено свойство "NO ACTION", то сервер сгенерирует ошибку, вызванную ограничением этого внешнего ключа. Подобную ситуацию можно наблюдать, если из таблицы "Goods" попытаться удалить запись, на которую имеется ссылка в дополнительной таблице "GoodsProvider", и, соответственно, есть связанная с ней запись в таблице "Provider". В этом случае сервер сгенерирует ошибку с кодом 547 и текстом:
Для определения взаимосвязи между таблицами можно использовать последовательность действий, описанную выше. После получения данных о наличии логической связи между таблицами "Provider" и "Goods" можно сформировать сообщение об ошибке, отражающей наличие этой логической связи между таблицами, например, следующего содержания:
3. Выполняется попытка дублирования связи для записей таблиц, участвующих в логической связи "многие ко многим"
В этой ситуации в дополнительную таблицу добавляется запись, которая будет нарушать ограничение уникальности, в которое входят поля внешних ключей. Для таблиц "Goods" и "Provider", между которыми реализуется логическая связь "многие ко многим", это будет соответствовать ситуации нарушения ограничения первичного ключа дополнительной таблицы "GoodsProvider". Сервер сгенерирует ошибку с кодом 547 и текстом:
Для выявления логической связи "многие ко многим" между таблицами можно использовать запрос 10 (несколько изменённый вариант запроса 9), в котором в качестве параметра <Ограничение уникальности> указывается имя ограничения уникальности из текста ошибки.
OBJECT_NAME (fk.referenced_object_id) AS [TABLE],
OBJECT_NAME (ixc.object_id) AS [Supplementary TABLE],
OBJECT_NAME (fk.object_id) AS [FOREIGN KEY],
ix.name AS[UNIQUE name],
[UNIQUE type] =
CASE
WHEN ix.is_primary_key = 1 THEN 'Primary key'
WHEN ix.is_unique_constraint = 1 THEN 'Unique constraint'
WHEN ix.is_unique = 1 THEN 'Unique index'
END
FROM
sys.foreign_keys fk, sys.foreign_key_columns fkc,
sys.indexes ix, sys.index_columns ixc
WHERE
ix.name = '<Ограничение уникальности>'
AND (fkc.constraint_object_id = fk.object_id)
AND ix.object_id = fk.parent_object_id
AND ixc.object_id = ix.object_id
AND ixc.column_id = fkc.constraint_column_id
Запрос 10. Получение списка внешних ключей, связанных ограничением уникальности.
4.2. Сообщения об ошибках для таблиц с логической связью "один ко многим"
Можно выделить несколько задач, для решения которых применяется логическая связь "один ко многим":
- Реализуется внешний ключ, ссылающийся на туже самую таблицу (рекурсивно).
- Главная таблица используется для ограничения значений, вводимых в поле или несколько полей подчинённой таблицы.
- Связь реализуется между таблицами для повышения степени нормализации базы данных.
Отражение в сообщениях об ошибках специфики использования логической связи "один ко многим" позволяет во многих случаях повысить их информативность.
1. Внешний ключ, ссылающийся на собственную таблицу
В этой ситуации в таблице создается внешний ключ со ссылкой на другое поле той же самой таблицы. По этому признаку данную ситуацию можно отличить от других случаев использования внешнего ключа. Для такого типа ключей могут возникать все варианты ошибок, которые характерны для внешних ключей (раздел 3).
Примером является внешний ключ "FK_Provider_Provider" таблицы "Provider" (скрипт 7), который реализует связь между полями "ID" и "IDMain" для определения главного поставщика среди группы поставщиков. При попытке добавить запись в таблицу "Provider" и указать значение для поля "IDMain", которое не соответствует ни одному из значений поля "ID", сервер сгенерирует ошибку с кодом 547 и текстом:
При попытке удаления записи, на которую ссылается поле "IDMain", сервер генерирует ошибку с кодом 547 и текстом:
"sales", TABLE "dbo.Provider", COLUMN 'IDMain'.
Если выполнить запрос 5 со значением параметра "FK_Provider_Provider", то он вернет одну запись (таблица 5).
Таблица 5. Результат выполнения запроса 5 для внешнего ключа "FK_Provider_Provider".
Часть 1
Table
Table description
Column
Column description
Referenced table
Referenced table description
| Provider | Поставщики | IDMain | Главный поставщик | Provider | Поставщики |
продолжение Часть 1
| Referenced Column | Referenced column description | Unique referenced table | Unique type referenced table | Delete referential action |
| ID | Поставщик | PK_Provider | Primary key | NO_ACTION |
Часть 2
| Table | Table description | Column | Column description | Referenced table | Referenced table description |
| Provider | Поставщики | IDMain | Главный поставщик | Provider | Поставщики |
продолжение Часть 2
| Referenced Column | Referenced column description | Unique referenced table | Unique type referenced table | Delete referential action |
| ID | Поставщик | PK_Provider | Primary key | NO_ACTION |
Значения полей "TABLE" и "Referenced TABLE" запроса совпадают, что свидетельствует о том, что внешний ключ "FK_Provider_Provider" ссылается на собственную таблицу.
2. Ограничения значений подчинённой таблицы
Можно выделить два варианта реализации, которые используются для этого.
1. Внешний ключ осуществляет ссылку на поля главной таблицы, которые не входят в её первичный ключ
По этому признаку ситуацию можно отличить от других случаев использования внешнего ключа.
Примером такой взаимосвязи могут быть таблицы "SALES" и "Discount" (скрипт 11).
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value] [numeric](16, 2) NOT NULL,
[Title] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Discount] PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID скидки',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Discount', @level2type=N'COLUMN',@level2name=N'ID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Значение скидки',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Discount',
@level2type=N'COLUMN',@level2name=N'Value'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Название скидки',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Discount',
@level2type=N'COLUMN',@level2name=N'Title'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Скидки',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Discount'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Discount_Value] ON [dbo].[Discount]
([Value] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Скрипт 11. Создание таблицы "Discount" и внешнего ключа "FK_Sales_Discount" для таблицы "Sales" (вариант 1).
Для ограничения возможных значений скидок в поле "Discount" таблицы "Sales" используется внешний ключ "FK_Sales_Discount", ссылающийся на поле "Value" таблицы "Discount", которое входит в уникальный ключ "IX_Discount_Value".
2. Внешний ключ осуществляет ссылку на поля главной таблицы, входящие в её первичный ключ
Примером такой взаимосвязи может быть вариант, в котором для создания таблицы "Discount" используется ниже приведённый скрипт 12.
[Value] [numeric](16, 2) NOT NULL,
[Title] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Discount] PRIMARY KEY CLUSTERED(
[Value] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Значение скидки',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Discount', @level2type=N'COLUMN',@level2name=N'Value'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Название скидки',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Discount', @level2type=N'COLUMN',@level2name=N'Title'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Скидки',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Discount'
GO
ALTER TABLE [dbo].[Sales] WITH CHECK
ADD CONSTRAINT [FK_Sales_Discount] FOREIGN KEY([Discount])
REFERENCES [dbo].[Discount] ([Value])
GO
Скрипт 12. Создание таблицы "Discount" и внешнего ключа "FK_Sales_Discount" для таблицы "Sales" (вариант 2).
В отличие от предыдущего варианта (скрипт 11), поле "Value" таблицы "Discount" входит в её первичный ключ.
Оба варианта взаимосвязи позволяют реализовать ограничение значений поля "Discount" таблицы "Sales" значениями поля "Value" таблицы "Discount".
Выбор варианта, конечно же, будет определяться разработчиком БД. Если первый вариант (скрипт 11) позволяет выявить цель использования внешнего ключа, то при использовании второго варианта (скрипт 12) выявить назначение внешнего ключа без дополнительной информации практически невозможно, так как ссылка на поля первичного ключа главной таблицы используется и во многих других случаях применения внешнего ключа.
Выходом из создавшейся неопределённости может быть использование специальных сообщений об ошибках или хранение в БД дополнительной информации о назначении внешнего ключа.
Независимо от того, как реализована связь между таблицами (вариант 1 или 2), при возникновении ошибки для пользователя, скорее всего, будет важна информация о том, чем ограничиваются значения полей в подчинённой таблице. Поэтому в отличие от случая формирования сообщения для таблиц с логической связью "многие ко многим", в сообщении для пользователя во многих случаях будет необходима информация о полях главной таблицы, которые используются для ограничения значений подчинённой таблицы.
3. Увеличение степени нормализации БД
Характерной особенностью применения внешних ключей для повышения нормализации БД является использование во внешнем ключе полей главной таблицы, составляющих её первичный ключ. Для пользователя в такой ситуации обычно важна информация о взаимосвязи записей этих таблиц, но не о способе её реализации. Поэтому в сообщении об ошибке, как и в сообщениях для таблиц с логической связью "многие ко многим", обычно достаточно указать только таблицы, между которыми реализуется такая связь. Примером такой ситуации является взаимосвязь между таблицами "Goods" и "Sales" (скрипты 1 и 2), реализуемая с помощью внешнего ключа "FK_Sales_Goods".
Для получения информации о внешнем ключе может использоваться запрос 5. Поле "Unique type referenced table" этого запроса содержит информацию о типе ограничения (уникальный индекс, клавный или уникальный ключ), которое используется в главной таблице внешнего ключа для его реализации.
4.3. Сообщения об ошибках для таблиц с логической связью "один к одному"
Обычно такая логическая связь используется в ситуации, когда данные, которые могут быть, в общем-то, сохранены в одной записи таблицы, из-за требований проектирования БД необходимо хранить в разных таблицах. Если это вызвано необходимостью повышения производительности БД, то в подчинённой таблице обычно располагают дополнительные данные и могут, например, запрашиваться только при необходимости.
Подчиненная таблица в этом случае обычно содержит дополнительные данные и ссылается на поля первичного ключа главной таблицы. Дополнительным условием для подчинённой таблицы, которое определяет отношение "один к одному", является ограничение уникальности (уникальный индекс, первичный или уникальный ключ) для полей подчиненной таблицы, которые входят во внешний ключ.
Ниже приведён скрипт создания таблицы "GoodsImage", предназначенной для хранения изображений товаров. Связь таблицы "GoodsImage" с главной таблицей "Goods" реализуется с помощью внешнего ключа "FK_GoodsImage_Goods" (скрипт 13).
[GoodsCode] [int] NOT NULL,
[Photo] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_GoodsImage] PRIMARY KEY CLUSTERED([GoodsCode] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Код товара',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GoodsImage',
@level2type=N'COLUMN',@level2name=N'GoodsCode'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Фото товара',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GoodsImage',
@level2type=N'COLUMN',@level2name=N'Photo'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Изображения товаров' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GoodsImage'
GO
ADD CONSTRAINT [FK_GoodsImage_Goods] FOREIGN KEY([GoodsCode])
REFERENCES [dbo].[Goods] ([Code])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
Скрипт 13. Создание таблицы "GoodsImage".
Первичный ключ таблицы "GoodsImage" используется для контроля уникальности значений её поля "GoodsCode", которое, в соответствии с ограничением внешнего ключа "FK_GoodsImage_Goods", может принимать только значения, совпадающие с полем первичного ключа таблицы "Goods". Таким образом, внешний ключ "FK_GoodsImage_Goods" и первичный ключ "PK_GoodsImage" реализуют между таблицами "Goods" и "GoodsImage" логическую связь "один к одному".
Ошибки изменения данных таблицы "GoodsImage" могут быть обусловлены ограничениями уникальности. В качестве значений правил обновления и удаления для внешнего ключа "FK_GoodsImage_Goods" установлено значение "CASCADE". Поэтому внешний ключ может вызывать только ошибки при добавлении записи в таблицу "GoodsImage", если новое значение поля "GoodsCode" не соответствует ни одному значению поля "Code" таблицы "Goods".
Для выявления логической связи "один к одному" можно использовать запрос 5.
Заключение
Как было замечено при обсуждении внешних ключей, в ряде случаев тип логической связи между таблицами определить может быть сложно, поэтому в этих случаях могут использоваться специальные сообщения об ошибках. Другим вариантом формирования сообщения является использование дополнительной информации о логической связи между таблицами, которая может хранится в БД, например, в виде специальной таблицы и определяться разработчиков на этапе проектирования БД. В этом случае сообщения об ошибках могут формироваться на этих данных. Варианты создания специальных сообщений и формирование сообщений на основе дополнительной информации о логических связях между таблицами не являются взаимоисключающими и, конечно же, могут использоваться совместно.
Если же для реализации логических связей между таблицами используются триггеры, то в этом случае выявить взаимосвязи между таблицами на основе анализа структуры БД будет сделать сложно. В этом случае сообщения об ошибках могут формироваться в тригерах, например, с помощью функций PRINT и RAISEERROR.
Целью данной статьи является описание основных идей метода, который может использоваться для формирования информативных сообщений об ошибках баз данных для конечного пользователя.
Хотя за рамками статьи остались некоторые моменты реализации, хочется надеяться, что описанный в статье подход позволит уменьшить трудозатраты при разработке программного обеспечения, повысить его надежность и качество.