Обробка помилок і формування повідомлень про помилки для баз даних Microsoft SQL Server 2005/2008, Інтеграція додатків і даних, Бази даних, статті

Для програм, що працюють з БД, важливим є не тільки коректність обробки помилок БД, а й формування інформативних повідомлень про ці помилки. Наявність таких повідомлень дозволяє швидше виявляти причини помилок зміни даних БД та їх виправляти. Особливо це актуально для користувачів програмних продуктів, яким у більшості випадків може бути не відома не тільки структура конкретної БД, а й теоретичні основи реляційних баз даних. Хоча Microsoft SQL Server і формує досить докладні повідомлення про помилки обмежень бази даних, але при безпосередній передачі їх користувачам в більшості випадків, складно розраховувати на те, що йому вдасться виявити за таким повідомленням причину помилки. Можна виділити кілька основних причин виникнення такої ситуації:


  1. Використання в клієнтських програмах позначень таблиць і полів (користувацьких назв), відмінних від їх імен в базі даних. Наприклад, таблиця бази даних має ім’я “Goods”, а в клієнтському додатку дані цієї таблиці можуть відображатися в довіднику з назвою “Товари” або “Продукція”. Відповідно, якщо виникне помилка, викликана обмеженням цієї таблиці, то сервер сформує повідомлення, в якому фігуруватиме назва таблиці “Goods”, а не користувальницьке назву таблиці, яке відоме користувачеві.

  2. Відсутність у користувачів достатніх знань про структуру бази даних і особливості зберігання даних в ній. Погодьтеся, що досить абсурдно вимагати від касира теоретичних знань про бази даних лише для того, щоб він зміг прийняти платіж від клієнта.

  3. Залежність повідомлення про помилку від призначення програми. Навіть для програм, що працюють з однією і тією ж БД, може знадобитися формування різних повідомлень про одну й ту ж помилку. Наприклад, в програмі для редагування даних БД користувачем повідомлення повинно бути: “Товар з такою назвою вже зареєстровано! Перевірте назву товару!”. А в програмі імпорту даних може знадобитися повідомлення про цю ж помилку але із зовсім іншим змістом: “Імпортовані дані дублюються – перевірте дані про імпортовані товари!”.

  4. При формуванні повідомлень про помилки сервер баз даних не враховує логічної структури бази даних. Наприклад, обмеження зовнішніх ключів можуть використовуватися для реалізації різних логічних зв’язків між таблицями: один до одного, один до багатьох, багато до багатьох. Для формування зрозумілого для користувача повідомлення про помилку для кожної з цих ситуацій повинні формуватися окремі повідомлення, інакше вони не тільки не допоможуть користувачеві виявити причину помилки, але і можуть ще більше його заплутати.

  5. Складність формування повідомлень для деяких помилок, викликаних обмеженнями БД. Наприклад, в обмеженнях CHECK для таблиць можуть використовуватися досить складні запити та умови. Тому формування повідомлень на основі їх аналізу може виявитися досить складним завданням.

Сукупність цих факторів звичайно призводить до того, що формування повідомлень навіть про однотипних помилках реалізується індивідуально для кожної транзакції. У результаті код для формування повідомлень про помилки виявляється розподіленим по всьому додатку, що ускладнює його супровід. Через необхідність написання коду практично для кожної можливої ​​помилки, частина помилок, навіть про яких розробнику відомо, виявляються без відповідних повідомлень для користувача. В результаті достатньо інформативні повідомлення формуються тільки для деякої частини помилок, в інших же випадках користувачеві залишається задовольнятися повідомленнями від самого сервера баз даних. Як вже було розглянуто вище, інформативність таких повідомлень в більшості випадків недостатня для виявлення причини виниклої проблеми та її усунення.


Це призводить до того, що формування повідомлень про помилки БД досить часто сильно відрізняється від обробки самих помилок. При обробці помилок БД зазвичай вдається виробити загальну стратегію, що дозволяє локалізувати їх обробку в одному або декількох місцях програми. Для формування повідомлень про помилки БД можна застосувати такий же підхід, якщо повідомлення про помилки умовно розділити на дві групи:



  1. універсальні повідомлення, які формуються на основі аналізу структури БД;

  2. спеціальні повідомлення, які визначаються індивідуально для кожної помилки.

Це дозволить локалізувати формування повідомлень про помилки в одному місці програми і спростити загальну схему обробки помилок БД.


Універсальні та спеціальні повідомлення про помилки


Універсальні повідомлення про помилки


Формування повідомлень цього типу грунтується на тому, що в тексті помилки Microsoft SQL Server вказує інформацію про помилку і об’єкті БД, який став причиною її виникнення. Такими об’єктами зазвичай є обмеження БД, такі як, головні, унікальні та зовнішні ключі, унікальні індекси, обмеження “not null” та ін На основі цих повідомлень можуть бути в ряді випадків сформовані зрозумілі для користувача повідомлення про помилки. Для формування таких повідомлень необхідна інформація про логічну структуру бази даних, яка може бути отримана з системного каталогу БД. Інформація про користувача назвах таблиць і їхніх полів може зберігається в БД і використовуватися при формуванні повідомлень. Для цього, наприклад, можуть використовуватися розширені властивості таблиць та їх полів.


Спеціальні повідомлення про помилки


Необхідність використання спеціальних повідомлень може виникнути у випадку, якщо універсальне повідомлення про помилку з якихось причин не відображає реальну причину помилки або не може бути сформовано. Прикладом останнього випадку є обмеження CHECK для таблиць. В умовах обмежень можуть використовуватися досить складні запити та умови, аналіз яких може виявитися досить складним завданням. Тому для цих обмежень часто зручніше використовувати повідомлення, які визначаються на етапі розробки.


Можна виділити дві групи спеціальних повідомлень про помилки БД:



Інформація про першої групи повідомлень може зберігатися в БД. Друга група повідомлень може зберігатися як в додатку (наприклад, у вигляді ресурсів або в спеціальному файлі), так і в БД, але із зазначенням додатка для якого вона призначена. При виборі варіанту зберігання спеціальних повідомлень в ряді випадків має сенс враховувати, що може виявитися зручною можливість коректування таких повідомлень без перекомпіляції додатки. Це може, наприклад, знадобитися якщо розробкою займається одна група співробітників, а підтримкою – інша група. В цьому випадку група підтримки може самостійно доповнювати або змінювати спеціальні повідомлення про помилки.


Хоча до сих мова йшла тільки про текстових повідомленнях, але необхідно брати до уваги, що спеціальні повідомлення можуть бути, наприклад, у форматі HTML або RTF, містити не тільки текст, але й зображення, посилання на розділи документації або довідкової системи.


Спеціальні повідомлення в базі даних можуть зберігатися, наприклад, в окремій таблиці або як розширені властивості обмежень БД. Останній варіант може виявитися в ряді випадків більш переважним, ніж використання для цього окремої таблиці. При зміні імені обмеження або його видаленні не буде потрібно додатково проводити зміни в окремій таблиці. Розширені властивості обмеження будуть видалені при видаленні обмеження, а зміна імені обмеження не потребують коригування розширених властивостей (якщо не потрібна зміна самого спеціального повідомлення).


Комплексне використання спеціальних і універсальних повідомлень про помилки


Універсальні та спеціальні повідомлення про помилки дозволяють реалізувати досить гнучку схему формування повідомлень про помилки БД для кінцевого користувача. Як вже було описано вище, для кожної помилки може використовуватися кілька повідомлень:



  1. спеціальне повідомлення рівня програми;

  2. спеціальне повідомлення рівня БД;

  3. універсальне повідомлення, що формується на основі аналізу структури БД;

  4. вихідне повідомлення Microsoft SQL Server.

Типи повідомлень перераховані в порядку пріоритету їх вибору при виникненні помилки (рис. 1).



Рис. 1. Послідовність формування повідомлення про помилку бази даних.


Якщо для помилки не задано спеціальне повідомлення (рівня БД або рівня програми), то буде виводитися універсальне повідомлення про помилку, яке формується на основі аналізу структури БД. Якщо з якихось причин воно не може бути сформований, то виводиться вихідне повідомлення від Microsoft SQL Server.


Якщо для помилки визначено спеціальне повідомлення, то буде виводитися саме це повідомлення. Причому спеціальне повідомлення рівня програми має в цій схемі “більший пріоритет”, ніж спеціальне повідомлення рівня БД.


Описана послідовність формування універсальних і спеціальних повідомлень про помилки орієнтована на те, що для більшості помилок можуть бути сформовані універсальні повідомлення на основі аналізу фізичної і логічної структури БД. Для тих же помилок, для яких ці повідомлення недостатньо інформативні, або для випадків, коли окремі додатки вимагають особливих формулювань повідомлень про помилки, можуть використовуватися спеціальні повідомлення рівня БД та рівня програми.


Якщо спеціальні повідомлення зберігаються в БД, то ця схема може дозволити розширити можливості групи технічної підтримки програми (або програмного комплексу), даючи їй можливість створювати спеціальні повідомлення (рівня БД та рівня програми) для тих помилок БД, для яких вона вважає це необхідним.


Формування універсальних повідомлень


Як вже говорилося вище, основна ідея використання універсальних повідомлень полягає в тому, що вони можуть бути сформовані автоматично на основі даних про помилку від Microsoft SQL Server і про структуру БД.


Припустимо, в таблицю “Goods” (скрипт 1) користувач намагається додати товар з назвою (поле “Title”), яке вже є в таблиці.


SET ANSI_NULLS ON
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 = NMS_Description, @ value = NКод товару,
  @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
  @level1name=NGoods, @level2type=NCOLUMN, @level2name=NCode
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NНазваніе,
  @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
  @level1name=NGoods, @level2type=NCOLUMN, @level2name=NTitle
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NЦена,
  @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
  @level1name=NGoods, @level2type=NCOLUMN, @level2name=NPrice
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NТовари,
  @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NGoods;
ALTER TABLE [dbo].[Goods] WITH CHECK ADD CONSTRAINT [CK_Price] CHECK (([Price]>=(0)))
GO
EXEC sys.sp_addextendedproperty @name=NMS_Description,
@ Value = NЦена товару не може бути негативною, @ level0type = NSCHEMA,
  @level0name=Ndbo, @level1type=NTABLE, @level1name=NGoods,
  @level2type=NCONSTRAINT, @level2name=NCK_Price
GO

Скрипт 1. Створення таблиці “Goods”.


Сервер в цьому випадку генерує помилку, так як поле “Title”, в якому зберігається назву товару, включено в унікальний індекс “IX_Goods_Title”:


Cannot INSERT duplicate KEY row IN object dbo.Goods WITH UNIQUE INDEX 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 можна виділити ряд найбільш часто зустрічаються помилок, обумовлених обмеженнями БД:



Серед цих причин найбільшу складність в інтерпретації викликають обмеження зовнішніх ключів. Обумовлено це тим, що вони використовуються для реалізації різних логічних зв’язків між таблицями. Це так ж ускладнює аналіз і деяких інших обмежень, тому що в ряді випадків вони так само використовуються для реалізації цих логічних зв’язків.


Тому далі спочатку розглядається відносно прості випадки формування універсальних повідомлень, а потім розглядається створення повідомлень для обмежень зовнішніх ключів та їх взаємозв’язок з логічними зв’язками між таблицями.


1. Не вказано значення поля, обов’язкове для заповнення (обмеження NOT NULL)


При виникненні даної ситуації в результаті додавання або редагування запису сервер генерує помилку з кодом 515 і текстом:


Cannot INSERT the value NULL INTO COLUMN <Поле>, TABLE <БД>. <Власник>. <Таблиця>; COLUMN does NOT allow NULL. <INSERT/UPDATE> fails.

Для операцій вставки і зміни запису ці повідомлення про помилку різняться назвою операції, при виконанні якої сталася помилка. Для відображення цього моменту у вище наведених повідомленнях використовується позначення


“<insert/updata>”


для вказівки того, що в залежності від ситуації в повідомленні буде присутній одне зі слів, вказаних через роздільник “/”.


Для отримання користувацького назви таблиці, до якої належить поле, можна використовувати запит 2.


SELECT value AS table_description
FROM fn_listextendedproperty (MS_Description, schema, <Схема>,
table, “<Таблиця>“, NULL, NULL)

Запит 2. Отримання користувацького назви таблиці.


Як параметри запиту <Схема> і <Таблиця> необхідно вказати відповідно ім’я схеми і таблиці, з повідомлення про помилку.


Для отримання користувацького назви поля таблиці можна використовувати запит 3.


SELECT value AS field_description
FROM fn_listextendedproperty (MS_Description, schema, <Схема>,
table, “<Таблиця>“, COLUMN, “<Поле>“)

Запит 3. Отримання користувацького назви поля таблиці.


Як параметри запиту <Схема>, <Таблиця>, <Поле> необхідно відповідно вказати імена схеми, таблиці та поля таблиці з повідомлення про помилку.


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


Необхідно вказати значення поля “Користувацька назву поля” в таблиці “Користувацька назву таблиці” при додаванні / зміну нового запису.

Звичайно, зміст повідомлення може змінюватись в залежності від типу програми та стилю спілкування програми з користувачем. Наприклад, у програмі таблиці можуть називатися довідниками. Якщо зміна виконується користувачем в інтерактивному режимі, то, швидше за все, не буде потрібно додатково уточнювати, при виконанні якої саме операції сталася помилка: при додаванні запису (операція INSERT) або при її зміні (операція UPDATE). Але якщо помилка сталася при імпорті даних в БД, то інформації про операцію може виявитися досить актуальною.


Як приклад можна розглянути спробу додавання запису в таблицю “Goods” (скрипт 1.1) зі значенням NULL для строкового поля “Title”:


INSERT INTO Goods (Code, Title, Price) VALUES (1, NULL, 100)

Сервер в цьому випадку генерує повідомлення про помилку:


Cannot INSERT the value NULL INTO COLUMN Title, TABLE Sales.dbo.Goods; COLUMN does NOT allow NULL. INSERT fails.

Для отримання користувальницьких назв таблиці “Goods” та її поля “Title” можна використовувати запити 2 і 3. В результаті виконання запиту 2 буде отримано користувальницьке назву таблиці “Goods” – “Товари”, а в результаті виконання запиту 3 користувальницьке назва її поля “Title” – “Назва”. Використовуючи отримані користувача назви таблиці та поля можна сформувати повідомлення про помилку:


Необхідно вказати значення поля “Назва” в таблиці “Товари” при додаванні нового запису.

2. Порушена унікальність значення поля або набору полів


Необхідність введення унікального значення поля може вимагатися в основному в трьох випадках:



  1. поле входить в головний ключ;

  2. поле включено в унікальний ключ;

  3. поле входить в унікальний індекс.

У перших двох випадках Microsoft SQL Server генерує один і той же код помилки 2627, але різні повідомлення. У першому випадку текст помилки має зміст:


Violation of PRIMERY KEY constraint <Головний ключ>. Cannot <insert/updata> duplicate KEY IN object <Схема>. <Таблиця>.

У другому випадку в тексті помилки вказується, що причиною є унікальний ключ:


Violation of UNIQUE KEY constraint <Унікальний ключ>. Cannot <insert/updata> duplicate KEY IN object <Схема>. <Таблиця>.

При порушенні унікального індексу Microsoft SQL Server генерує помилку з кодом про 2601 і текстом:


Cannot <insert/updata> duplicate KEY row IN object <Схема>. <Таблиця> WITH UNIQUE INDEX <Індекс>.

Як можна помітити, в цих повідомленнях не вказується поле або набір унікальних полів, які були причиною помилки. Цю інформацію можна отримати на основі інформації про ключ або індексі.


Для отримання інформації про поля, що входять в індекс, головний або унікальний ключ, можна використовувати запит 4.


SELECT i.name [Name],
  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 “будуть різними і містити інформацію про кожного з полів. Повідомлення в цьому випадку може мати, наприклад, наступний вигляд:


Поєднання значень полів <Користувацька назву поля 1>, <Користувацька назву поля 2> в таблиці <Користувацька назва таблиці> має бути унікальним.

Зокрема, для прикладу порушення обмеження унікального індексу “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. Обмеження зовнішніх ключів


Складність формування повідомлень про помилки на основі аналізу структури бази даних (універсальних повідомлень), викликаних обмеженнями зовнішніх ключів, пов’язана в першу чергу тим, що зовнішні ключі використовуються для реалізації різних логічних зв’язків між таблицями. Тому для формування інформативного повідомлення крім отримання інформації про зовнішній ключі, що викликала помилку, потрібно так само додатковий аналіз зв’язків таблиць зовнішнього ключа з іншими таблицями для визначення логічного зв’язку між ними.


Можна виділити кілька причин, що призводять до порушення обмежень зовнішніх ключів:



  1. В підпорядковану таблицю додається запис, в якому для поля зовнішнього ключа немає відповідного значення в головній таблиці. Аналогічна ситуація відбувається при зміні значення поля підлеглої таблиці у разі, якщо нового значення поля немає в головній таблиці.

  2. У зовнішній таблиці виконується спроба видалення даних, на які є посилання в підпорядкованої таблиці. При цьому у визначенні зв’язку між таблицями вказано обмеження “NO ACTION” для операції видалення даних. При такого зв’язку Microsoft SQL Server не дозволяє видаляти дані з зовнішньої таблиці, якщо в підпорядкованої таблиці є записи пов’язані з видаляється записом.

  3. Ситуація аналогічна п.2, але тільки для операції зміни даних у зовнішній таблиці з обмеженням “NO ACTION”. Microsoft SQL Server в цьому випадку генерує ті ж помилки, що і при видаленні даних.

При додаванні або зміна значення поля в підлеглій таблиці, для якого немає відповідного значення у зовнішній таблиці Microsoft SQL Server генерує помилку з кодом 547 і текстом:


The <INSERT/UPDATE> statement conflicted WITH the FOREIGN KEY constraint <Зовнішній ключ>. The conflict occurred IN DATABASE <База даних>, TABLE <Схема>. <Зовнішня таблиця>.

Якщо у зовнішній ключ входить всього одне поле, то повідомлення про помилку має більш докладний зміст:


The statement conflicted with the FOREIGN KEY constraint <Зовнішній ключ>. The conflict occurred in database <База даних>, table <Схема>. <Зовнішня таблиця>, column <Поле зовнішньої таблиці>.

Для отримання інформації про поля зовнішньої і підпорядкованої таблиць, що входять у зовнішній ключ, можна використовувати наведений нижче запит 5.


SELECT
    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. Отримання інформації про зовнішній ключі <Зовнішній ключ>.


Як параметр запиту <Зовнішній ключ> необхідно вказати назву зовнішнього ключа з повідомлення про помилку. Поля в результуючому запиті мають таке значення:



Таким чином, можна отримати інформацію про поля, що входять у зовнішній ключ з боку зовнішньої і посилальної таблиць. Це дає можливість сформувати повідомлення, досить точно відображає причину помилки, наприклад, наступного змісту:


Значення поля <Користувацька назву поля посилальної таблиці> таблиці <Користувацька назву посилальної таблиці> повинні відповідати значенням поля <Користувацька назву поля зовнішньої таблиці> таблиці <Користувацька назву зовнішньої таблиці>.

У разі видалення або зміни поля зовнішньої таблиці, що входить у зовнішній ключ до правила оновлення “NO ACTION”, і якщо в посилальної таблиці є записи, що посилаються на змінну запис, то Microsoft SQL Server згенерує помилку з кодом 547 і текстом:


The DELETE statement conflicted with the FOREIGN KEY constraint <Зовнішній ключ>. The conflict occurred in database <База даних>, table <Схема>. <Зовнішня таблиця>.

Якщо у зовнішній ключ входить всього одне поле, то в тексті помилки додатково буде вказано назву поля зовнішньої таблиці, що використовується в зовнішньому ключі:


The DELETE statement conflicted with the FOREIGN KEY constraint <Зовнішній ключ>. The conflict occurred in database <База даних>, table <Схема>. <Зовнішня таблиця>, column <Поле зовнішньої таблиці>.

Для визначення взаємозв’язку між зовнішньою і посилальної таблицями можна використовувати запит 5.


Якщо зв’язок між таблицями здійснюється по одному полю, то повідомлення для користувача може мати, наприклад, такий зміст:


Не можна модифікувати записи з таблиці <Користувацька назву зовнішньої таблиці>, Для яких значення поля <Користувацька назву поля зовнішньої таблиці> використовуються в підпорядкованої таблиці <Користувацька назву посилальної таблиці> в якості значень для поля <Користувацька назву поля посилальної таблиці>.

Якщо ж зв’язок між таблицями здійснюється по декількох полях, то повідомлення може мати, наприклад, такий зміст:


Не можна модифікувати записи з таблиці <Опис зовнішньої таблиці>, Для яких значення полів <Користувацька назву поля 1 зовнішньої таблиці>, <Користувацька назву поля 2 зовнішньої таблиці> використовуються в підпорядкованої таблиці <Користувацька назву посилальної таблиці> в якості значень для полів <Користувацька назву поля 1 посилальної таблиці>, <Користувацька назву поля 2 посилальної таблиці>.

Аналогічна ситуація відбувається, якщо обмеження “NO ACTION” встановлено для операції зміни даних. Microsoft SQL Server в цьому випадку генерує помилку з тим же кодом і текстом, в якому вказується, що помилка сталася при виконанні операції зміни даних:


The UPDATE statement conflicted WITH the FOREIGN KEY constraint <Зовнішній ключ>. The conflict occurred IN DATABASE <База даних>, TABLE <Схема>. <Зовнішня таблиця>.

Якщо у зовнішній ключ входить всього одне поле, то в повідомленні про помилку буде додатково зазначена назва поля зовнішньої таблиці, що використовується в зовнішньому ключі:


The UPDATE statement conflicted WITH the FOREIGN KEY constraint <Зовнішній ключ>. The conflict occurred IN DATABASE <База даних>, TABLE <Схема>. <Зовнішня таблиця>, COLUMN <Поле зовнішньої таблиці>.

Як приклад можна розглянути помилки зовнішніх ключів для таблиць “Goods” і “Sales” (скрипт 6) пов’язаних з допомогою зовнішнього ключа “FK_Sales_Goods”.


SET ANSI_NULLS ON
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 = NMS_Description, @ value = NТовар,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NSales, @level2type=NCOLUMN, @level2name=NGoods
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NЦена,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NSales, @level2type=NCOLUMN, @level2name=NQty
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NСкідка,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NSales, @level2type=NCOLUMN, @level2name=NDiscount
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NСумма,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NSales, @level2type=NCOLUMN, @level2name=NSumm
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NПродажі,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NSales
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:


The INSERT statement conflicted WITH the FOREIGN KEY constraint FK_Sales_Goods. The conflict occurred IN DATABASE Sales, TABLE dbo.Goods, COLUMN Code.

Якщо виконати запит 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 згенерує помилку з текстом:


The DELETE statement conflicted WITH the REFERENCE constraint FK_Sales_Goods. The conflict occurred IN DATABASE Sales, TABLE dbo.Sales, COLUMN Goods.

Після аналізу структури БД з допомогою запиту 5 (табл. 2) можна сформувати повідомлення про помилку:


Не можна модифікувати запис з таблиці “Товари”, Значення поля “Код товару” якій використовуються в підпорядкованої таблиці “Продажі” в якості значень для поля “Товар”.

4. Зовнішні ключі та логічні зв’язки між таблицями


Для формування інформативних повідомлень про помилки зовнішніх ключів необхідно наявність інформації про логічні зв’язки між таблицями для реалізації яких вони використовуються.


Прикладом може бути використання зовнішнього ключа для посилання на власну таблицю. Зрозуміло, що в цьому випадку повідомлення про помилку повинно відрізнятися від випадку, коли головна і підпорядкована таблиці є різними.


Іншим прикладом є формування повідомлень для таблиць з логічної зв’язком “багато до багатьох”. Зазвичай такий зв’язок між таблицями реалізується за допомогою додаткової таблиці, і зовнішні ключі безпосередньо не пов’язують таблиці між якими реалізується логічний зв’язок.


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


4.1. Повідомлення про помилки для таблиць з логічної зв’язком “багато до багатьох”


Зазвичай такий зв’язок реалізується за допомогою додаткової таблиці. У додатковій таблиці створюються зовнішні ключі, які посилаються на таблиці, між якими і реалізується зв’язок “багато до багатьох”. Щоб уникнути дублювання зв’язків між таблицями, у додатковій таблиці на поля, які входять в ці зовнішні ключі, накладається обмеження унікальності (унікальний індекс, первинний або унікальний ключ). Обидва зовнішніх ключа додаткової таблиці посилаються на поля таблиць логічного зв’язку “багато до багатьох”, які входять в їх первинні ключі. Для створення нового зв’язку між записами логічно пов’язаних таблиць в додаткову таблицю додається новий запис, а для видалення наявної зв’язку між записами таблиць з логічної зв’язком “багато до багатьох” віддаляється відповідний запис з додаткової таблиці, тобто всі зміни виконуються тільки в додатковій таблиці, правильність яких контролюється обмеженнями цієї таблиці.


Якщо сталася помилка, викликана обмеженням зовнішнього ключа, то для виявлення логічного зв’язку “багато до багатьох” можна використовувати її характерні ознаки:



  1. Зовнішній ключ, обмеження якого викликало помилку, входить також до обмеження унікальності (унікальний індекс, первинний або унікальний ключ). Це відповідає структурі додаткової таблиці, використовуваної для реалізації логічного зв’язку “багато до багатьох”.

  2. В це обмеження унікальності додаткової таблиці входять поля іншого зовнішнього ключа. Інші поля в це обмеження унікальності додаткової таблиці не входять. Таблиці, на які посилаються ці зовнішні ключі, як раз і пов’язані логічної зв’язком “багато до багатьох”.

Як приклад такої логічного зв’язку можна розглянути взаємозв’язок між таблицями “Goods” (скрипт 1) і “Provider” (скрипт 7), в якості сполучною використовується таблиця “GoodsProvider” (скрипт 8).


   


SET ANSI_NULLS ON
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 = NMS_Description, @ value = NПоставщік,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NProvider,
   @level2type=NCOLUMN, @level2name=NID
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NНазваніе,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
   @level1name=NProvider, @level2type=NCOLUMN, @level2name=NName
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NАдрес,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
   @level1name=NProvider, @level2type=NCOLUMN, @level2name=NAddress
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NГлавний постачальник,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NProvider,
   @level2type=NCOLUMN, @level2name=NIDMain
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NПоставщікі,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE, @level1name=NProvider
GO
ALTER TABLE [dbo].[Provider] WITH CHECK ADD CONSTRAINT [FK_Provider_Provider]
   FOREIGN KEY([IDMain]) REFERENCES [dbo].[Provider] ([ID])
GO

Скрипт 7. Створення таблиці “Provider”.


SET ANSI_NULLS ON
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 = NMS_Description, @ value = NТовар,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
   @level1name=NGoodsProvider, @level2type=NCOLUMN, @level2name=NGoodsID
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NПоставщік,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
   @level1name=NGoodsProvider, @level2type=NCOLUMN, @level2name=NProviderID
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NЦена,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
   @level1name=NGoodsProvider, @level2type=NCOLUMN, @level2name=NPrice
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NТовари і постачальники,
   @level0type=NSCHEMA, @level0name=Ndbo, @level1type=NTABLE,
   @level1name=NGoodsProvider
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:


INSERT INTO dbo.GoodsProvider (GoodsID, ProviderID, Price) VALUES (1, 10, 110);

То це призведе до помилки 574 з текстом:


The INSERT statement conflicted WITH the FOREIGN KEY constraint “FK_GoodsProvider_Provider”. The conflict occurred IN DATABASE “sales”, TABLE “dbo.Provider”, COLUMN ID.

Для формування повідомлення про помилку спочатку необхідно визначити наявність логічного зв’язку “багато до багатьох” між таблицями, т.к. в загальному випадку про наявність логічних зв’язків між таблицями може бути нічого не відомо. Для отримання інформації про зовнішній ключі, що викликала помилку, можна використовувати запит 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 повертає інформацію про зовнішні ключах, які пов’язані обмеженням унікальності.


SELECT
   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. Зовнішні ключі, пов’язані обмеженням унікальності.


Поля запиту мають таке значення:



Як параметр <Зовнішній ключ> запиту 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 і текстом:


The DELETE statement conflicted WITH the REFERENCE constraint “FK_GoodsProvider_Goods”. The conflict occurred IN DATABASE “sales”, TABLE “dbo.GoodsProvider”, COLUMN GoodsID.

Для визначення взаємозв’язку між таблицями можна використовувати послідовність дій, описану вище. Після отримання даних про наявність логічного зв’язку між таблицями “Provider” і “Goods” можна сформувати повідомлення про помилку, що відбиває наявність цієї логічного зв’язку між таблицями, наприклад, такого змісту:


Не можна видалити запис з таблиці “Постачальники” так як вона пов’язана з однією або кількома записами таблиці “Товари”.

3. Виконується спроба дублювання зв’язку для записів таблиць, що беруть участь в логічного зв’язку “багато до багатьох”


У цій ситуації в додаткову таблицю додається запис, який буде порушувати обмеження унікальності, в яке входять поля зовнішніх ключів. Для таблиць “Goods” і “Provider”, між якими реалізується логічний зв’язок “багато до багатьох”, це буде відповідати ситуації порушення обмеження первинного ключа додаткової таблиці “GoodsProvider”. Сервер згенерує помилку з кодом 547 і текстом:


Violation of PRIMARY KEY constraint PK_GoodsProvider. Cannot INSERT duplicate KEY IN object dbo.GoodsProvider.

Для виявлення логічного зв’язку “багато до багатьох” між таблицями можна використовувати запит 10 (трохи змінений варіант запиту 9), в якому як параметр <Обмеження унікальності> вказується ім’я обмеження унікальності з тексту помилки.


SELECT
   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. Реалізується зовнішній ключ, що посилається на ту саму таблицю (рекурсивно).

  2. Головна таблиця використовується для обмеження значень, що вводяться в поле або кілька полів підпорядкованої таблиці.

  3. Зв’язок реалізується між таблицями для підвищення ступеня нормалізації бази даних.

Відображення в повідомленнях про помилки специфіки використання логічного зв’язку “один до багатьох” дозволяє в багатьох випадках підвищити їх інформативність.


1. Зовнішній ключ, що посилається на власну таблицю


У цій ситуації в таблиці створюється зовнішній ключ з посиланням на інше поле тієї ж самої таблиці. За цією ознакою дану ситуацію можна відрізнити від інших випадків використання зовнішнього ключа. Для такого типу ключів можуть виникати всі варіанти помилок, які характерні для зовнішніх ключів (розділ 3).


Прикладом є зовнішній ключ “FK_Provider_Provider” таблиці “Provider” (скрипт 7), який реалізує зв’язок між полями “ID” і “IDMain” для визначення головного постачальника серед групи постачальників. При спробі додати запис в таблицю “Provider” і вказати значення для поля “IDMain”, яке не відповідає жодному із значень поля “ID”, сервер згенерує помилку з кодом 547 і текстом:


The INSERT statement conflicted WITH the FOREIGN KEY SAME TABLE constraint “FK_Provider_Provider”. The conflict occurred IN DATABASE “sales”, TABLE “dbo.Provider”, COLUMN ID.

При спробі видалення запису, на яку посилається поле “IDMain”, сервер генерує помилку з кодом 547 і текстом:


The DELETE statement conflicted WITH the SAME TABLE REFERENCE constraint “FK_Provider_Provider”. The conflict occurred IN DATABASE
“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).


CREATE TABLE [dbo].[Discount](
  [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 = NMS_Description, @ value = NID знижки,
  @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,
  @level1name=NDiscount, @level2type=NCOLUMN,@level2name=NID
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NЗначеніе знижки,
  @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDiscount,
  @level2type=NCOLUMN,@level2name=NValue
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NНазваніе знижки,
  @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDiscount,
  @level2type=NCOLUMN,@level2name=NTitle
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NСкідкі,
  @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDiscount
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.


CREATE TABLE [dbo].[Discount](
  [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 = NMS_Description, @ value = NЗначеніе знижки,
  @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDiscount,   @level2type=NCOLUMN,@level2name=NValue
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NНазваніе знижки,
  @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDiscount,   @level2type=NCOLUMN,@level2name=NTitle
EXEC sys.sp_addextendedproperty @ name = NMS_Description, @ value = NСкідкі,
  @level0type=NSCHEMA,@level0name=Ndbo, @level1type=NTABLE,@level1name=NDiscount
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) у

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*