SQL хранимая процедура для трансформации таблицы. Динамический запрос.

В данном посте я немного затрону вопросы создания T-SQL хранимых процедур и динамических запросов.

В одном из моих предыдущих постов - http://vasyniuk.blogspot.com/2012/03/pivot-unpivot.html я рассказывал об обработке результирующих наборов данных с использованием оператора T-SQL PIVOT. В данном посте я хочу развить эту тему и перевести ее на более высокий уровень – на уровень использования хранимых процедур SQL.

В вышеупомянутом посте шел разговор о преобразовании небольшой таблицы, которая являлась результатом выполнения UDF. Как быть если необходимо обработать результат выполнения UDF с большим множеством полей, который после преобразования должен превратиться в таблицу большого размера? Если случай не тривиальный и результирующая таблица большого размера должна содержать, допустим, 20 столбцов, то придется вручную прописывать эти 20 имен столбцов в операторе выборки SELECT, что не есть хорошо.

Очевидно, что в таком случае необходима автоматизация процесса преобразования наборов данных.  Единственный разумный вариант работы с такой таблицей – динамическая обработка ее столбцов в запросе с использованием оператора PIVOT.

Чтобы извлечь максимальную выгоду из такой ситуации, я буду использовать не прямой запроc, а хранимую процедуру Transact-SQL. Сразу скажу, что разработка такой процедуры с точки зрения программирования под MS SQL Server – несет в себе много скрытых препятствий для неопытных разработчиков. Создается впечатление, что в Microsoft попытались максимально усложнить жизнь разработчикам, наполнив T-SQL большим количеством всевозможных ограничений.

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

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

Сценарий разработки

Как вариант, можно спроектировать хранимую процедуру, в которую будет передаваться таблица-переменная. Внутри пакета хранимой процедуры будет происходить обработка этой таблицы с последующим формированием результирующего набора данных – преобразованной таблицы.

CREATE PROC [dbo].[piv_StatTable] @temp_table dbo.EStatTT READONLY

AS

     SET NOCOUNT ON

     DECLARE @rows varchar(8000),@querry varchar(8000)

     CREATE TABLE #df(element_name nvarchar(4),period_id tinyint, value smallint)

     INSERT #df SELECT * FROM @temp_table

     SELECT @rows=COALESCE(@rows+',['+CAST(period_id AS varchar)+']',

                                        '['+CAST(period_id AS varchar)+']')

     FROM @temp_table

     GROUP BY period_id

    

     SET @querry='SELECT * FROM #df PIVOT (MAX(value) FOR period_id IN('+@rows+')) AS tbl_pivot'

     EXEC(@querry)

    

     DROP TABLE #df

GO

Разбор полетов

На входе в процедуру передается переменная @temp_table типа dbo.EStatTT. Тип данных EStatTT – это определяемый пользователем табличный тип. Табличный тип данных TABLE – относительно молодой поддерживаемый тип в MS SQL Server по сравнению с такими привычными многим типами как int или varchar. Оперировать этим типом данных можно, допустим, в UDF - возвращая таблицу данных, как результат выполнения функции. В контексте передачи переменных хранимым процедурам тип TABLE использовать не получится, хотя хотелось бы.

Ограничение: для этого необходимо сначала создать собственный тип данных - определяемый пользователем табличный тип (по аналогии с определением класса на C#).

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

CREATE TYPE [dbo].[EStatTT] AS TABLE(

     [element_name] [nvarchar](4) NULL,

     [period_id] [tinyint] NULL,

     [value] [smallint] NULL

)

GO

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

Еще одним нюансом использования переменных табличного типа в качестве параметров, передаваемых хранимым процедурам, является ограничение на выполнение над ними DML операций в теле хранимых процедур. Как видно, после декларации параметра в объявлении хранимой процедуры следует инструкция READONLY, которая и определяет поведение объекта-таблицы внутри процедуры.

Зачем необходима временная таблица #df?

Как видно из листинга, #df используется в строковой переменной @querry при составлении тела динамического запроса.

Ограничение: мы не можем использовать переменную @temp_table подобным образом – это своего рода ограничение видимости локальных переменных.

@temp_table – является локальной переменной, объявление и инициализация которой будут происходить вне пакета разрабатываемой хранимой процедуры. В динамических запросах нельзя использовать локальные переменные, которые были объявлены до вызова самого запроса. Собственно, именно по этой причине мы вынуждены использовать временную таблицу, которая заполняется данными из @temp_table путем выполнения простого запроса INSERT…SELECT.

Переменная строкового типа @rows содержит строку сформированную с помощью оператора COALESCE из значений столбца “period_id” таблицы, которую мы передаем процедуре. С первого взгляда оператор инициализации переменной @rows может показаться немного запутанным, но в нем нет ничего экстраординарного.

Оператор COALESCE – это один из CASE-производных операторов, который упрощает синтаксис Transact-SQL. Подробно о нем можно прочитать в MSDN - http://msdn.microsoft.com/ru-ru/library/ms190349.aspx. Чтобы было понятнее для чего он служит в данном случае, можно провести аналогию с оператором C# “??”:

string rows = (rows + ",[" + period_id.ToString() + "]") ?? ("[" + period_id.ToString() + "]");

Уникальность значений обеспечивается путем использования инструкции GROUP BY в конце запроса выборки.

Ну и, собственно, сам динамический запрос.

В переменной @querry формируется тело запроса с использованием оператора PIVOT. Для динамического создания новых столбцов таблицы используются значения вышеупомянутой строки @rows. Завершает все инструкция EXECUTE, в которую в качестве параметра передается строка @querry.

Использование

      Как правильно использовать созданную хранимую процедуру? Посмотрим на скриншот:

       Сценарий использования процедуры должен быть построен следующим образом:

1)    Для начала необходимо объявить переменную UDTT (User defined table type) табличного типа, о котором я писал выше – инструкция DECLARE.

2)    Затем - подготовить таблицу для передачи в хранимую процедуру (произвести инициализацию этой переменной, наполнив таблицу данными). За это отвечают два составных оператора INSERT…SELECT.

3)    После этого следует вызов хранимой процедуры с таблицей-параметром в операторе EXECUTE.

Ссылки

MSDN table-valued parameters - http://msdn.microsoft.com/en-us/library/bb510489.aspx

SQL.RU динамические запросы - http://www.sql.ru/faq/faq_topic.aspx?fid=104

Профессиональное руководство по SQL Server: хранимые процедуры, XML, HTML

The guru's guide to SQL Server stored procedures, XML, and HTML
Автор: Хендерсон (Henderson)

“Вместо того чтобы просто показывать различные трюки и тонкости синтаксиса, эта книга обучает философии программирования в Transact-SQL. Она объясняет, как применять эту философию для создания собственных способов кодирования и решения повседневных проблем.”

     Несмотря на данное заявление автора, в книге, к счастью, в достаточном количестве присутствуют и описания особенностей синтаксиса T-SQL, и идиоматические приемы программирования.

Общее впечатление

     Хорошая книга, но с небольшими недостатками. Читать легко и интересно несмотря на небольшую беспорядочность или хаотичность структуры материала, причем интересно читать даже “левые” главы :) (кроме 24, 25 и 26).

Целевая аудитория

     Книга ориентирована на опытных разработчиков. В первую очередь - для тех, кто хорошо знаком с MS SQL и Transact-SQL. Думаю, для новичков она будет сложна и, как следствие, скучна.

Особенности

     Упор в книге автор делает все таки на хранимые процедуры.

     Во введении автор приводит полезные книги разных тематик, которые он сам использовал и рекомендует для обучения: ООП, паттерны проектирования, тестирование, рефакторинг. Я сам пару взял для себя.

   Материал книги ориентирован на версии SQL Server 2000 и 2005, вследствие чего информация местами устарела.

     Немного огорчили главы 24, 25 и 26. Это уже перебор и, имхо, не допустимо в технической литературе. Либо автор был морально истощен под конец написания книги, либо - решил показать всю многогранность своего таланта… L

     Еще один недостаток – автор затрагивает  достаточное количество “левых” тем в узкоспециализированной (как должно быть по логике вещей судя по названию) книге. Местами возникает ощущение, что читаешь какой-то обычный учебник по информатике для студентов 1-го курса, где поверхностно описываются разные технологии.

По главам

   1 глава посвящена хранимым процедурам. Среди прочих описываются системные процедуры: sp_helptext (просмотр исходного кода процедуры), sp_spaceused (построение отчета об утилизации дискового пространства БД). Рассказывается об особенностях использования настроек и параметров окружения SQL Server. Временные, системные и внутренние хранимые процедуры.

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

   3 глава - конкретные советы с примерами. Вся “соль” то ценное, что может быть в любой книге. Рассматриваются паттерны проектирования путем проведения аналогии с  эквивалентными идиомами T-SQL.

     5 глава содержит много теоретических сведений по проектированию. Детально на примере рассматривается 3 стадии разработки проектов: анализ (построение модели бизнесс-процессов), проектирование (нормализированная E-R-модель), разработка. Есть словарь терминов E-R-моделирования. Описаны 5-нормальных форм. На примере рассмотрена работа с пакетом CASE-средств моделирования Grandite  Silverrun BPM, ERX, RDM.

     Глава 11 содержит довольно интересную информацию о трансформации данных БД в HTML, я не сталкивался с подобными приемами использования T-SQL.

     12 глава - введение в XML. Довольно обширная глава с описанием стандарта XML. Автор рассказывает об XML, его особенностях, об валидации DTD и XML Schema, об использовании XSLT для преобразования XML в степени достаточной для того, чтоб разобраться что к чему. Затрагиваются вопросы эффективности использования XML в сравнении с HTML. Есть ссылки на литературу по XML и описание программных инструментальных средств для работы с XML/DTD/XSLT.

     В главе посвященной автоматизации и COM  автор не упомянул о настройке конфигурации SQL Server-а для выполнения OA-процедур, и если вы будете пытаться выполнить код из листингов  примеров – то, скорее всего, получите сообщение об ошибке.

     Глава 21 – хранимые процедуры для администрирования. Интересные примеры хранимых процедур в связке с использованием xp_cmdshell и применением специальных приемов и особенностей программирования T-SQL. Дается очень неплохое описание некоторых не документированных процедур, но, к сожалению, большинство из них актуальны для SQL Server версий 2000-2005.

    22 глава. Полезным дополнением к материалу книги является описание не документированных команд DBCC (порядка 35 штук), не документированных Transact-SQL функций и флагов трассировки SQL Server.

     Очень хороша глава 23. Она посвящена разработке dll-библиотек расширенных хранимых процедур, что можно трактовать, как работу с хранимыми процедурами на практически самом низком уровне (ниже только ASM J). Присутствуют: листинги кода *.dll–ок процедур на С/C++; описание способа подключения откомпилированных процедур к SQL Server; примеры использования.

Трансформация результирующих наборов данных. Операторы PIVOT и UNPIVOT.

     Иногда при работе с БД может возникнуть необходимость изменения порядка отображения результирующего набора данных (результата выполнения выражения возвращающего табличное значение). Например, таблицу-результат работы оператора выборки или результат выполнения пользовательской функции можно экспортировать в  *.csv файл и далее работать с ним используя MS Excel.

      Зачем может понадобиться изменять набор данных перед выполнением операции экспорта? Как вы увидите далее на примере, проектирование реляционных БД имеет свои нюансы и, зачастую, то, как данные содержатся в таблицах БД диаметрально противоположно тому, как они должны содержаться в таблицах Excel или Word для обеспечения удобства работы с ними.

      Итак – пример. Допустим, имеется некая UDF, результатом работы которой является таблица статистических данных, состоящая из трех столбцов (1-й результирующий набор на скриншоте).

Скриншот 1.

      Лично мне было удобно использовать данные в виде 1-го результирующего набора для построения диаграмм в MS Word. Но что делать, если есть необходимость отобразить их в виде привычной многим таблицы (в виде 2-го результирующего набора на скриншоте)?

      Очевидно, что их необходимо своего рода “транспонировать” по аналогии с операцией транспонирования матрицы, но, как видите это должно быть не совсем транспонирование в его традиционном понимании.

      Для выполнения таких задач служит оператор Transact-SQL – PIVOT. Как написано MSDN (http://msdn.microsoft.com/ru-ru/library/ms177410.aspx): ”Оператор PIVOT разворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов, а также, в случае необходимости, объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных.”

      Конкретно для нашего примера мы видим, что значения столбца “period_id” после выполнения оператора PIVOT преобразуются в поля результирующего набора, значения этих полей берутся из исходного столбца “value” и все это группируется по полю “element_name”. Важно отметить, что в операторе PIVOT я использовал агрегатную функцию MAX(). Использование этой функции в данном случае обусловлено лишь синтаксическими особенностями оператора PIVOT, которые требуют использования статистической функции.

      Как выполнить обратное по отношению к результату, полученному с использованием оператора PIVOT, преобразование результирующего набора данных, когда необходимо “транспонировать” столбцы таблицы-результата выполнения запроса в строки? Для проведения подобных манипуляций используется оператор UNPIVOT.

Скриншот 2.

      На 2-ом скриншоте видно, что результатом выполнения 2-ой инструкции SELECT, в которой использован оператор UNPIVOT, является набор данных, в котором поля исходного набора (результата выполнения UDF): pid1…4 - стали соответствующими строками сформированного поля “pids”. Также появилось поле “ods”, содержащее прежние значения столбцов pid1…4.

      Для полной ясности еще одна цитата из MSDN (по тому же референсу): “оператор UNPIVOT не является в точности обратным оператору PIVOT. Оператор PIVOT производит статистическую обработку и слияние нескольких строк в единую выходную строку. Оператор UNPIVOT не восстанавливает исходные возвращающие табличное значение выражения, так как строки были объединены”.

Self referencing many-to-many на MS SQL Server и Access. Особенности. Часть 2.

...продолжение

Особенности проектирования (SQL Server)

      Итак, в этом посте будут затронуты особенности построения БД с использованием связи self referencing many-to-many на SQL Server.

      Все исходные данные и условия те же, что и в первой части.

      Допустим, мы создали две таблицы (пока без ограничений FK):

USE [stones1]

GO

CREATE TABLE [dbo].[stones](

      [id] [smallint] NOT NULL,

      [name] [nvarchar](35) NOT NULL,

      CONSTRAINT [PK_stones_1] PRIMARY KEY ([id] ASC)

      )

GO

CREATE TABLE [dbo].[stf](

      [stone_id] [smallint] NOT NULL,

      [form_id] [smallint] NOT NULL,

 CONSTRAINT [PK_stf] PRIMARY KEY([stone_id] ASC,[form_id] ASC)

)

GO

Теперь наложим ограничения FK на поля отношения STF:

USE [stones1]

GO

ALTER TABLE [dbo].[stf]  WITH CHECK ADD  CONSTRAINT [FK_stf_stones_id1] FOREIGN KEY([stone_id])

REFERENCES [dbo].[stones] ([id])

ON DELETE CASCADE

ON UPDATE CASCADE

GO

ALTER TABLE [dbo].[stf]  WITH CHECK ADD  CONSTRAINT [FK_stf_stones_form1] FOREIGN KEY([form_id])

REFERENCES [dbo].[stones] ([id])

ON DELETE CASCADE

ON UPDATE CASCADE

GO

      При попытке выполнить эти пакеты вы получите сообщение об ошибке следующего вида:

Или, если задавать FK в конструкторе – то приблизительно такое сообщение:

      Как видим – SQL Server не позволяет спроектировать БД подобным образом :( …

      Согласно информации по референсу http://support.microsoft.com/kb/321843/en-us?fr=1, (далее практически дословно) вы получаете ошибку  1785 потому, что в СУБД SQL Server таблица не может появляться более одного раза  в  списке всех каскадных ссылочных действий, которые являются результатом вызова инструкций DELETE или UPDATE.

     Более продвинутые в MS SQL гуру на RSDN (http://www.rsdn.ru/forum/db/3081247.flat.aspx) обьясняют это спецификой алгоритма определения циклов от мелкомягких, и это безобразие тянется еще с 2000 года, и менять никто ничего принципиально не собирается (конечно, лезть в старый код и переписывать рабочий  алгоритм, со всеми вытекающими… я б сам не стал :))

Кто виноват, и что делать?

     Очевидно, что это упущение разработчиков мелкомягких. Ведь поддерживание  целостности данных на максимально возможном уровне – одна из основных задач СУБД и она определена в стандарте ANSI/ISO. В данном случае, стараниями разработчиков, этот максимум занижен путем наложенного ограничения функционала СУБД (видимо, перестарались). Можно конечно возразить, что конкретные реализации условий целостности отличаются в разных СУБД, но “отличная от иной реализация” и “отсуствие реализации” – это разные вещи.

      Решение данной проблемы саппорт целиком и полностью возлагает на наши плечи :) (в том-же референсе предлагается использовать триггеры в случае необходимости обесппечения целостности и максимальной гибкости). То же решение предлагается и на RSDN, и на SQL.RU (http://www.sql.ru/forum/actualthread.aspx?tid=595772).

Каковы будут наши дальнейшие действия?

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

     Итак, для успешного удаления записи из таблицы STONES мы должны сначала обработать таблицу STF, так как у нас наложены ограничения внешних ключей с  правилами обновления и удаления RESTRICT (по умолчанию), и в случае непосредственной попытки выполнить инструкцию DELETE - мы получим сообщение об ошибке.

Простейший возможный сценарий работы триггера можно составить таким образом:

 - Проверить количество связанных дочерних записей в таблице STF и в случае успеха – удалить.

 - Проверить количество связанных родительских записей в таблице STF и в случае успеха – также удалить.

 - Удалить запись таблицы STONES

USE [stones1]

GO

CREATE TRIGGER [dbo].[trg_stones_IOD] ON [dbo].[stones]

INSTEAD OF DELETE

AS

BEGIN

      SET NOCOUNT ON

IF(SELECT COUNT(*) FROM stf, deleted WHERE stf.stone_id=deleted.id)>0

      BEGIN

            DELETE stf FROM stf, deleted WHERE stf.stone_id=deleted.id

      END

     

      IF(SELECT COUNT(*) FROM stf, deleted WHERE stf.form_id=deleted.id)>0

      BEGIN

            DELETE stf FROM stf, deleted WHERE stf.form_id=deleted.id

      END

   

    DELETE stones FROM stones,deleted WHERE stones.id=deleted.id

   

END

GO

      Триггер для обновления INSTEAD OF UPDATE пишется аналогичным образом и здесь я его приводить не буду.

      Вот и все. Мы добились требуемой архитектуры БД с использованием связи many-to-many и каскадным удалением\обновлением данных.

Утилита BatchAccess для MS Access

     BatchAccess - это утилита, написанная под .NET Framework, которая позволяет расширить основные возможности разработчиков по работе с БД MS Access на "низком уровне", то есть - на уровне SQL скрипта.

      Разработчик - Русские Информационные Технологии.
      Сайт - http://www.russianit.ru/software/batchaccess/
      Мне посчастливилось :) работать с версией - 1.4.

      На мой взгляд - довольно полезная вещь. MS Access можно назвать визуально-ориентированной средой проектирования, ведь любой, кому приходилось работать с Access знает, что большая часть процесса проектирования БД реализована посредством работы с пользовательским интерфейсом. Основной недостаток такого подхода к проектированию - часть "кухни" скрыта от разработчика, то есть - физически невозможно получить доступ к SQL коду при выполнении тех или иных манипуляций. В общем, кто работал с Access - те в курсе о чем идет речь.

     Итак, BatchAccess позволяет работать с БД более привычным образом, посредством выполнения SQL-операторов.

Требования

      Для работы приложения требуется соответственно предустановленный .NET Framework (на сайте заявлена минимальная версия .NET Framework 1.1), MDAC и Microsoft Jet Driver 4.0. Наличие самого MS Access, естественно, не требуется.

      Как видно - весьма непритязательные требования к ПО.

Возможности

      Утилита позволяет работать как через консоль, так и через пользовательский интерфейс.

Среди заявленных возможностей по работе с БД присутствуют: создание БД, выполнение скриптов над БД, работа со структурой и данными (создание\получение), экспорт в CSV-файл и др.

Вьювер. Структура БД.

    Работа

      Лично я использовал BatchAccess для получения структуры БД. Используя программный интерфейс это можно сделать следующим образом:

  1) Для того, чтобы открыть существующую БД необходимо выполнить команду меню Database → Select Database и выбрать соответствующий *.mdb-файл в появившемся FileOpenDialog.

  

  2) Далее, для экспорта структуры БД в *.sql-файл необходимо выполнить команду меню Database → Restore Structure Script и, соответственно, задать имя файла в появившемся диалоге.

  

  3) После этого можно открыть *.sql-файл (меню File → Open) и посмотреть скрипт.

sql-скрипт. Структура БД.

      

      На RSDN - http://www.rsdn.ru/article/files/progs/BatchAccess.xml присутствует обширное описание всех остальных возможностей программы, и исчерпывающее описание работы с консольной версией.

Self referencing many-to-many на MS SQL Server и Access. Особенности. Часть 1.

  Иногда при разработке БД могут возникать нестандартные случаи, требующие использования особых приемов проектирования. В данном посте я расскажу именно о таком особом случае, с которым мне пришлось столкнуться.

self referencing many-to-many

       Речь пойдет о проектировании структуры БД состоящей из двух таблиц с использованием связи self referencing many-to-many для одной таблицы, и вспомогательной таблицы взаимосвязей типа предок-потомок (если из вышенаписанного ничего не ясно – дочитайте до начала иллюстраций :)). Плюс при всем этом - будут затронуты нюансы обеспечения целостности данных (каскадное удаление\обновление) зависящие от конкретной используемой "платформы".

     Нужно отметить, что данная проблема слабо освещена в сети, и объяснение некоторых нюансов работы конкретной СУБД Access мне так и не удалось найти, возможно, в силу того, что подобные ситуации редко возникают в повседневной практике разработки БД.

Постановка задачи

      Итак, сформулирую рабочую задачу на абстрактном примере:

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

а) отдельным минеральным видом

б) минеральной разновидностью для одного или (внимание!) нескольких минеральных видов.

     Каким образом можно спроектировать БД, для хранения информации о минералах с учетом ограничений определенных условиями, и обеспечить целостность данных?

Проектирование (Access)

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

     Так как и минеральные виды и разновидности – это сущности одного типа, то для хранения информации о них необходимо одно отношение STONES, в котором будут определены поля id, и name. Для хранения информации о взаимосвязях (предок-потомок) между минералами необходимо отношение MTF (сокр. – minerals to forms) с полями stone_id, form_id. Ограничения накладываемые условиями предметной области не позволяют нам использовать еще одно дополнительное поле в таблице STONES для характеристики взаимосвязей между минералами, (так как один минерал может быть предком для нескольких потомков и, в свою очередь, один потомок может иметь нескольких предков), поэтому необходимость наличия отдельного отношения взаимосвязей является абсолютно очевидной.


     Прежде чем перейти к SQL Server, я спроектирую эту БД на Access, чтобы можно было убедиться в возможности построения такой структуры отношений.

   Итак, у нас имеется два отношения STONES и MTF. Целостность отношения MTF обеспечивается составным PK из соответствующих полей stone_id и form_id . Каждая запись в таблице будет уникальной. Отношение STONES будет иметь PK на поле id.

   После завершения проектирования структуры БД можно перейти к схеме данных и приступить к проектированию связей между отношениями. Ссылочная целостность отношений MTF и STONES обеспечивается ограничением FK для каждого из двух полей отношения MTF на PK отношения STONES, однозначно идентифицируя взаимоотношения предок-потомок между ключевыми сущностями.


     После определения последнего из двух FK для отношения MTF мы увидим следующую ужасающую картину:

      Как говорят буржуи: оО WTF? Смотрим код.

CREATE TABLE stf (

    form_id                 Long NOT NULL,

    stone_id                Long NOT NULL,

    CONSTRAINT PrimaryKey PRIMARY KEY (stone_id, form_id)

);

CREATE TABLE stones (

    id                      Long NOT NULL,

    stone_name              Text(80) WITH COMP,

    CONSTRAINT PrimaryKey UNIQUE (id),

    CONSTRAINT PrimaryKey1 PRIMARY KEY (id)

);

  

ALTER TABLE stf

    ADD CONSTRAINT stonesstf FOREIGN KEY (form_id) REFERENCES stones
ON UPDATE CASCADE
ON DELETE CASCADE;

ALTER TABLE stf

    ADD CONSTRAINT stonesstf1 FOREIGN KEY (stone_id) REFERENCES stones
ON UPDATE CASCADE
ON DELETE CASCADE;

        Что же это за таблица STONES_1? Дубликат отношения STONES? Судя по коду – вроде бы нет. В общем, я так и не разобрался, что это за безобразие.

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

Ситуацию удалось немного прояснить следующим образом:

      Я просто разбил отношение STONES на два отношения – STONES и STONEFORMS со связью между ними one-to-one. Как видно по диаграмме, тут все обошлось без фокусов и, видимо, дополнительная таблица на предыдущей диаграмме – это специфика Access-а.

      

      Но самое важное здесь не то, каким образом происходит отображение связей, а то - как они обеспечивают целостность данных. Как видно из листинга кода, ограничения внешних ключей определены с правилами обновления\удаления CASCADE. ч.т.д.

Итог по Access-у

    Итак, как можно видеть, Access позволяет проектировать подобную структуру БД с обеспечением каскадного обновления и удаления данных, чего не скажешь о SQL Server.

Продолжение во второй части...