 |
���������� ������-������ 2009'3
|
|
|
Сообщения об ошибках ограничений внешних ключей на примере БД Firebird
Опубликовано: 09.07.2010
Версия текста: 1.1
Данная статья является продолжением статьи [1]. Хочется обратить внимание на то, что в предыдущей статье рассматривался лишь один из возможных вариантов сообщений об ошибках, формируемых на основе анализа структуры БД. В зависимости от типа и назначения программы сообщения об ошибках могут довольно сильно различаться. Если программа предназначена для интерактивного изменения данных БД, то указание в сообщении имени изменяемой таблицы может оказаться излишним, так как в большинстве случаев пользователю она известна. Но, например, в утилите импорта-экспорта информация о таблице, изменение данных которой привело к ошибке, может оказаться необходимой.
Форма сообщений также может довольно сильно варьироваться. Например, при нарушении уникального индекса "IDX_GOODS_TITLE", используемого для контроля уникальности поля "TITLE" таблицы "GOODS" (скрипт 1.1), могут использоваться такие сообщения об ошибке, как:
- Значение поля "Название" таблицы "Товары" должно быть уникальным!
- Товар с таким названием уже зарегистрирован! Проверьте название товара!
- В справочнике товаров не может быть товаров с одинаковыми названиями!
Скрипт 1.1. Создание таблицы "GOODS".
CREATE DOMAIN D_INTEGER AS INTEGER;
CREATE DOMAIN D_MONEY AS NUMERIC(15,2);
CREATE DOMAIN D_STRING100 AS VARCHAR(100)
CHARACTER SET WIN1251 COLLATE WIN1251;
CREATE TABLE GOODS(
GDS_CODE D_INTEGER NOT NULL,
GDS_NAME D_STRING100 NOT NULL,
GDS_PRICE D_MONEY NOT NULL);
ALTER TABLE GOODS ADD CONSTRAINT PK_GOODS PRIMARY KEY (GDS_CODE);
CREATE UNIQUE INDEX IDX_GDS_NAME ON GOODS (GDS_NAME);
COMMENT ON TABLE GOODS IS 'Товары';
COMMENT ON COLUMN GOODS.GDS_CODE IS 'Код';
COMMENT ON COLUMN GOODS.GDS_NAME IS 'Название';
COMMENT ON COLUMN GOODS.GDS_PRICE IS 'Цена';
|
Если сравнить эти сообщения и комментарии для таблицы "GOODS" и её полей, то можно заметить, что формирование первого сообщения является наиболее простым вариантом (который и используется в большинстве примеров предыдущей статьи). Для формирования двух других сообщений может потребоваться лексический синтез, но это уже отдельная задача. Хотя эти сообщения и различаются, но во всех случаях указывается информация об объекте БД, для которого нарушено ограничение уникальности – это поле ""Название" таблицы "Товары". Эта информация как раз и может быть получена при анализе структуры БД.
В разделе 2.1.3 статьи [1] были рассмотрены ошибки, обусловленные ограничениями внешних ключей. Но во многих случаях могут быть сформированы более информативные варианты сообщений пользователю, если учитывать назначение внешних ключей и логические связи между таблицами. Примером может являться использование внешнего ключа для ссылки на собственную таблицу (раздел 2.1). Понятно, что в этом случае сообщение об ошибке должно отличаться от случая, когда главная и подчинённая таблица являются различными. Другим примером является формирование сообщений для таблиц с логической связью "многие ко многим". Обычно такая связь между таблицами реализуется с помощью дополнительной таблицы, и внешние ключи непосредственно не связывают таблицы, между которыми реализуется логическая связь (раздел 1). Поэтому если использовать сообщения об ошибке, сформированные на основе только информации об ограничениях между таблицами, то они, скорее всего, не смогут адекватно отразить взаимосвязи между таблицами, которые видит конечный пользователь.
Далее рассматриваются различные варианты использования внешних ключей и возможности выявления этих ситуаций.
1. Сообщения об ошибках для таблиц с логической связью «многие ко многим»
Обычно такая связь реализуется с помощью дополнительной таблицы. В дополнительной таблице создаются внешние ключи, которые ссылаются на таблицы, между которыми и реализуется связь "многие ко многим". Чтобы избежать дублирования связей между таблицами, в дополнительной таблице на поля, которые входят в эти внешние ключи, накладывается ограничение уникальности (уникальный индекс, первичный или уникальный ключ). Оба внешних ключа дополнительной таблицы ссылаются на поля таблиц логической связи "многие ко многим", которые входят в их первичные ключи. Для создания новой связи между записями логически связанных таблиц в дополнительную таблицу добавляется новая запись, а для удаления имеющейся связи между записями таблиц с логической связью удаляется соответствующая запись из дополнительной таблицы, т.е. все изменения выполняются только в дополнительной таблице, правильность которых контролируется ограничениями этой таблицы.
Если произошла ошибка, вызванная ограничением внешнего ключа, то для выявления логической связи "многие ко многим" можно использовать признаки её реализации:
- Внешний ключ, ограничение которого вызвало ошибку, входит также в ограничение уникальности (уникальный индекс, первичный или уникальный ключ). Это соответствует структуре связующей таблицы.
- В это ограничение уникальности связующей таблицы входят поля другого внешнего ключа.
- Другие поля в это ограничение уникальности связующей таблицы не входят.
- Связующая таблица имеет ровно два внешних ключа. Таблицы, на которых ссылаются эти ключи, как раз и связаны в логическую связь "многие ко многим".
В качестве примера такой логической связи можно рассмотреть взаимосвязь между таблицами "GOODS" (скрипт 1.1) и "PROVIDERS" (скрипт 1.2), в качестве связующей используется таблица "GOODS_PROVIDER" (скрипт 1.3).
Скрипт 1.2. Создание таблицы "PROVIDERS".
CREATE TABLE PROVIDERS (
PRD_ID D_INTEGER NOT NULL,
PRD_NAME D_STRING100 NOT NULL,
PRD_MAIN D_INTEGER);
ALTER TABLE PROVIDERS ADD CONSTRAINT PK_PROVIDERS PRIMARY KEY (PRD_ID);
ALTER TABLE PROVIDERS ADD CONSTRAINT FK_PRD_MAIN
FOREIGN KEY (PRD_MAIN) REFERENCES PROVIDERS (PRD_ID);
CREATE UNIQUE INDEX IDX_PRD_NAME ON PROVIDERS (PRD_NAME);
CREATE GENERATOR GEN_PROVIDERS_ID;
SET TERM ^ ;
CREATE OR ALTER TRIGGER PROVIDERS_BI FOR PROVIDERS
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.prd_id is null) then
new.prd_id = gen_id(gen_providers_id, 1);
end
^
SET TERM ; ^
COMMENT ON TABLE PROVIDERS IS 'Поставщики';
COMMENT ON COLUMN PROVIDERS.PRD_NAME IS 'Название';
COMMENT ON COLUMN PROVIDERS.PRD_MAIN IS 'Главный поставщик';
|
Скрипт 1.3. Создание таблицы "GOODS_PROVIDER".
CREATE TABLE GOODS_PROVIDER (
GP_GOODS D_INTEGER NOT NULL,
GP_PROVIDER D_INTEGER NOT NULL);
ALTER TABLE GOODS_PROVIDER ADD CONSTRAINT PK_GOODS_PROVIDER
PRIMARY KEY (GP_GOODS, GP_PROVIDER);
ALTER TABLE GOODS_PROVIDER ADD CONSTRAINT FK_GP_GOODS
FOREIGN KEY (GP_GOODS) REFERENCES GOODS (GDS_CODE);
ALTER TABLE GOODS_PROVIDER ADD CONSTRAINT FK_GP_PROVIDER
FOREIGN KEY (GP_PROVIDER) REFERENCES PROVIDERS (PRD_ID);
COMMENT ON TABLE GOODS_PROVIDER IS 'Поставщики товаров';
COMMENT ON COLUMN GOODS_PROVIDER.GP_GOODS IS 'Товар';
COMMENT ON COLUMN GOODS_PROVIDER.GP_PROVIDER IS 'Поставщик';
|
Поля "GOODS" и "PROVIDER" таблицы "GOODS_PROVIDER" входят в состав внешних ключей, которые ссылаются на таблицы "GOODS" и "PROVIDERS", между которыми и реализуется логическая связь "многие ко многим". Во внешние ключи "FK_GP_PROVIDER" и "FK_GP_GOODS" таблицы "GOODS_PROVIDER" входят поля составляющие первичные ключи таблиц "GOODS" и "PROVIDERS", между которыми реализуется логическая связь "многие ко многим". Чтобы избежать дублирования взаимосвязи между записями таблиц "GOODS" и "PROVIDERS" в таблице "GOODS_PROVIDER" используется первичный ключ "PK_GOODS_PROVIDER", в который входят поля обоих внешних ключей "FK_GP_PROVIDER" и "FK_GP_GOODS".
При необходимости реализовать связь между записями таблиц "GOODS" и "PROVIDER" добавляется запись в дополнительную таблицу "GOODS_PROVIDER", в которой в качестве значений полей "GP_GOODS" и "GP_PROVIDER" указываются значения полей "GOODS.GDS_CODE" и "PROVIDERS.PRD_ID", составляющих первичные ключи таблиц "GOODS" и "PROVIDERS". При необходимости разорвать связь между записями таблиц "GOODS" и "PROVIDERS" в дополнительной таблице "GOODS_PROVIDER" удаляется запись, в которой значения её полей "GP_GOODS" и "GP_PROVIDER" совпадают со значениями полей таблиц "GOODS.GDS_CODE" и "PROVIDERS.PRD_ID".
Для таблиц, участвующих в логической связи "многие ко многим", можно выделить несколько ситуаций, которые могут приводить к возникновению ошибок.
1.1. Делается попытка создания связи для несуществующей записи одной из таблиц, участвующих в логической связи "многие ко многим"
Это соответствует ситуации, когда выполняется попытка добавления в промежуточную таблицу записи, значения полей которой нарушают ограничение одного из внешних ключей.
Если такая ситуация произошла с таблицей "PROVIDER", то сервер сгенерирует ошибку с SQLCode -530 и текстом:
Violation of FOREIGN KEY constraint "FK_GP_PROVIDER" on table "GOODS_PROVIDER". Foreign key reference target does not exist.
|
Для формирования сообщения об ошибке сначала необходимо определить наличие логической связи "многие ко многим" между таблицами, т.к. в общем случае о наличии логических связей между таблицами может быть ничего не известно. Для получения информации о внешнем ключе, вызвавшем ошибку, можно использовать запрос 1.4.
Запрос 1.4. Получение информации о внешнем ключе.
SELECT
C1.RDB$CONSTRAINT_NAME CONST_NAME,
C1.RDB$RELATION_NAME TABLE_NAME, R1.RDB$DESCRIPTION TABLE_DESC,
I1.RDB$FIELD_NAME FIELD_NAME, RF1.RDB$DESCRIPTION FIELD_DESC,
C2.RDB$RELATION_NAME R_TABLE_NAME, R2.RDB$DESCRIPTION R_TABLE_DESC,
I2.RDB$FIELD_NAME R_FIELD_NAME, RF2.RDB$DESCRIPTION R_FIELD_DESC,
C2.RDB$CONSTRAINT_NAME R_CONST_NAME, C2.RDB$CONSTRAINT_TYPE R_CONST_TYPE
FROM
RDB$RELATION_CONSTRAINTS C1, RDB$REF_CONSTRAINTS RC,
RDB$RELATION_CONSTRAINTS C2, RDB$INDEX_SEGMENTS I1,
RDB$INDEX_SEGMENTS I2, RDB$RELATIONS R1,
RDB$RELATIONS R2, RDB$RELATION_FIELDS RF1, RDB$RELATION_FIELDS RF2
WHERE
C1.RDB$CONSTRAINT_NAME = :FKEY_NAME
AND C1.RDB$CONSTRAINT_NAME = RC.RDB$CONSTRAINT_NAME
AND C1.RDB$INDEX_NAME = I1.RDB$INDEX_NAME
AND C1.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
AND I1.RDB$FIELD_POSITION = I2.RDB$FIELD_POSITION
AND R1.RDB$RELATION_NAME = C1.RDB$RELATION_NAME
AND (RF1.RDB$SYSTEM_FLAG = 0)
AND (RF1.RDB$FIELD_NAME = I1.RDB$FIELD_NAME)
AND (RF1.RDB$RELATION_NAME = C1.RDB$RELATION_NAME)
AND RC.RDB$CONST_NAME_UQ = C2.RDB$CONSTRAINT_NAME
AND (C2.RDB$CONSTRAINT_TYPE =
'PRIMARY KEY' OR C2.RDB$CONSTRAINT_TYPE = 'UNIQUE')
AND I2.RDB$INDEX_NAME = C2.RDB$INDEX_NAME
AND R2.RDB$RELATION_NAME = C2.RDB$RELATION_NAME
AND (RF2.RDB$SYSTEM_FLAG = 0)
AND (RF2.RDB$FIELD_NAME = I1.RDB$FIELD_NAME)
AND (RF2.RDB$RELATION_NAME = C1.RDB$RELATION_NAME)
|
В качестве параметра запроса "FKEY_NAME" необходимо указать имя внешнего ключа, о котором необходимо получить информацию. Результат запроса содержит:
- "CONST_NAME" – имя внешнего ключа;
- "TABLE_NAME", "TABLE_DESC", "FIELD_NAME", "FIELD_DESC" – имя и описание подчинённой таблицы и её поля, которое входит во внешний ключ;
- "R_TABLE_NAME", "R_TABLE_DESC", "R_FIELD_NAME", "R_FIELD_DESC" – имя и описание главной таблицы и её поля, которое входит во внешний ключ;
- "R_CONST_NAME" – имя ключа главной таблицы, которое используется для реализации внешнего ключа, "R_CONST_TYPE". Тип этого ключа: "PRIMARY KEY" – первичный ключ или 'UNIQUE KEY' – уникальный ключ.
Результат запроса 1.4 для внешнего ключа "FK_GP_PROVIDER" из сообщения об ошибке представлен в таблице 1.1.
Как было описано выше, поля дополнительной таблицы "GOODS_PROVIDER", входящие во внешний ключ "FK_GP_PROVIDER", должны также входить в ограничение уникальности.
Таблица 1.1. Результат выполнения запроса 1.4 для внешнего ключа "FK_GP_PROVIDER".
|
CONST_NAME
|
TABLE_NAME
|
TABLE_DESC
|
FIELD_NAME
|
FIELD_DESC
|
|
|
FK_GP_PROVIDER
|
GOODS_PROVIDER
|
Поставщики товаров
|
GP_PROVIDER
|
Поставщик
|
|
|
R_TABLE_NAME
|
R_TABLE_DESC
|
R_FIELD_NAME
|
R_FIELD_DESC
|
R_CONST_NAME
|
R_CONST_TYPE
|
|
PROVIDERS
|
Поставщики
|
PRD_ID
|
Поставщик
|
PK_PROVIDERS
|
PRIMARY KEY
|
Таблица 1.1. Результат выполнения запроса 1.4 для внешнего ключа "FK_GP_PROVIDER".
|
CONST_NAME
|
TABLE_NAME
|
TABLE_DESC
|
FIELD_NAME
|
FIELD_DESC
|
|
|
FK_GP_GOODS
|
GOODS_PROVIDER
|
Поставщики товаров
|
GP_GOODS
|
Товар
|
|
|
R_TABLE_NAME
|
R_TABLE_DESC
|
R_FIELD_NAME
|
R_FIELD_DESC
|
R_CONST_NAME
|
R_CONST_TYPE
|
|
GOODS
|
Товары
|
GDS_CODE
|
Товар
|
PK_GOODS
|
PRIMARY KEY
|
Запрос 1.5 возвращает список всех ограничений уникальности (уникальные индексы, первичные и уникальные ключи), поля которых также входят во внешние ключи.
Запрос 1.5. Ограничения уникальности, включающие поля подчиненных таблиц внешних ключей.
SELECT
DISTINCT FC1.RDB$CONSTRAINT_NAME FKEY_NAME,
I.RDB$INDEX_NAME CONSTRAINT_NAME,
RC.RDB$CONSTRAINT_TYPE CONSTRAINT_TYPE
FROM
RDB$RELATION_CONSTRAINTS FC1
JOIN RDB$REF_CONSTRAINTS FRC
ON (FC1.RDB$CONSTRAINT_NAME = FRC.RDB$CONSTRAINT_NAME)
JOIN RDB$INDEX_SEGMENTS FI1 ON (FC1.RDB$INDEX_NAME = FI1.RDB$INDEX_NAME
AND FC1.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY')
JOIN RDB$INDICES I ON (I.RDB$RELATION_NAME = FC1.RDB$RELATION_NAME)
JOIN RDB$INDEX_SEGMENTS ISG ON (
ISG.RDB$INDEX_NAME = I.RDB$INDEX_NAME
AND ISG.RDB$FIELD_NAME = FI1.RDB$FIELD_NAME
AND I.RDB$UNIQUE_FLAG = 1
AND I.RDB$FOREIGN_KEY IS NULL
AND I.RDB$SYSTEM_FLAG = 0
AND I.RDB$INDEX_INACTIVE IS NULL)
LEFT JOIN RDB$RELATION_CONSTRAINTS RC ON (
RC.RDB$INDEX_NAME = I.RDB$INDEX_NAME
AND (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
OR RC.RDB$CONSTRAINT_TYPE = 'UNIQUE'))
|
Поля запроса имеют следующие значения: "FKEY_NAME" – имя внешнего ключа; "CONSTRAINT_NAME" – имя ограничения уникальности; CONSTRAINT_TYPE – тип ограничения уникальности: "PRIMARY KEY" – первичный ключ, "UNIQUE" – уникальный ключ, "NULL" – уникальный индекс.
Если поля ограничения уникальности входят в несколько внешних ключей, то запрос вернет для этого ограничения (поле запроса "CONSTRAINT_NAME") количество записей, соответствующее количеству таких внешних ключей (поле запроса "FKEY_NAME").
Могут быть также полезны варианты запроса 1.5, полученные добавлением условий "WHERE", которые в тексте запроса 1.5 записаны как комментарии и пронумерованы:
- Вариант 1. В качестве параметра указывается внешний ключ. Запрос возвращает ограничения уникальности, в которые входят поля подчиненной таблицы этого внешнего ключа.
- Вариант 2. В качестве параметра указывается ограничение уникальности. Запрос возвращает внешние ключи, поля подчиненных таблиц которых входят в это ограничение уникальности.
- Вариант 3. Является комбинацией вариантов 1 и 2 и возвращает список внешних ключей для ограничения уникальности (параметр "CONSTRAINT_NAME"), исключая указанный в качестве параметра внешний ключ (параметр "FKEY_NAME").
Если выполнить вариант 1 запроса 1.5 со значением "FK_GP_PROVIDER" параметра "FKEY_NAME", то он вернет одно ограничение уникальности – первичный ключ "PK_GOODS_PROVIDER" таблицы "GOODS_PROVIDER". Выполнение варианта 3 запроса 1.5 с указанием этого первичного ключа в качестве параметра запроса "CONSTRAINT_NAME" и ключа "FK_GP_PROVIDER" в качестве параметра "FKEY_NAME" вернет имя второго внешнего ключа "FK_GP_GOODS", поля которого входят в это ограничение уникальности. Запрос 1.4 для этого внешнего ключа вернет информацию, представленную в таблице 1.2.
Как видно из таблицы, внешний ключ "FK_GP_GOODS" ссылается на таблицу "GOODS", которая является одной из таблиц, между которыми реализуется связь "многие ко многим".
Значение поля "R_CONST_TYPE" таблиц 1.1 и 1.2 показывает, что внешние ключи "FK_GP_GOODS" и "FK_GP_PROVIDER" ссылаются на поля таблиц "GOODS" и "PROVIDERS", которые входят в их первичные ключи. Это подтверждает предположение о том, что между таблицами "GOODS" и "PROVIDERS" реализована связь "многие ко многим'.
В качестве варианта сообщения об ошибке может использоваться, например:
Нельзя связать запись из таблицы "Товары" с несуществующей записью из таблицы "Поставщики"
|
1.2. В одной из таблиц логической связи "многие ко многим", выполняется удаление записи, на которую имеются ссылки в другой таблице логической связи
Если для операции удаления внешнего ключа установлено свойство "NO ACTION", то сервер Firebird сгенерирует ошибку, вызванную ограничением этого внешнего ключа. Подобную ситуацию можно наблюдать, если, например, из таблицы "PROVIDERS" попытаться удалить запись, на которую имеется ссылка в дополнительной таблице "GOODS_PROVIDER", и, соответственно, есть связанная с ней запись в таблице "GOODS". В этом случае сервер сгенерирует ошибку с кодом -530 и текстом:
Violation of FOREIGN KEY constraint "FK_GP_PROVIDER" on table "GOODS_PROVIDER". Foreign key references are present for the record.
|
Для определения взаимосвязи между таблицами можно использовать последовательность действий, описанную выше. После получения данных о наличии логической связи между таблицами "PROVIDERS" и "GOODS" можно сформировать сообщение об ошибке, отражающей наличие этой логической связи между таблицами, например, следующего содержания:
Нельзя удалить запись из таблицы "Поставщики" так как она связана с одной или несколькими записями таблицы "Товары".
|
Таблица 2.1. Результат выполнения запроса 1.4 для внешнего ключа "FK_PRD_MAIN".
|
CONST_NAME
|
TABLE_NAME
|
TABLE_DESC
|
FIELD_NAME
|
FIELD_DESC
|
|
|
FK_PRD_MAIN
|
PROVIDERS
|
Поставщики
|
PRD_MAIN
|
Главный поставщик
|
|
|
R_TABLE_NAME
|
R_TABLE_DESC
|
R_FIELD_NAME
|
R_FIELD_DESC
|
R_CONST_NAME
|
R_CONST_TYPE
|
|
PROVIDERS
|
Поставщики
|
PRD_ID
|
Главный поставщик
|
PK_PROVIDERS
|
PRIMARY KEY
|
1.3. Выполняется попытка дублирования связи для записей таблиц, участвующих в логической связи "многие ко многим"
В этой ситуации в дополнительную таблицу добавляется запись, которая будет нарушать ограничение уникальности, в которое входят поля внешних ключей. Для таблиц "GOODS" и "PROVIDERS", между которыми реализуется логическая связь "многие ко многим", это будет соответствовать ситуации нарушения ограничения первичного ключа дополнительной таблицы "GOODS_PROVIDER". Сервер сгенерирует ошибку с кодом -803 и текстом:
Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values. Violation of PRIMARY or UNIQUE KEY constraint "PK_GOODS_PROVIDER" on table "GOODS_PROVIDER".
|
После выявления логической связи между таблицами, как описано выше (раздел 1.1), может быть сформировано сообщение об ошибке, учитывающее логическую связь между таблицами "GOODS" и "PROVIDERS".
2. Сообщения об ошибках для таблиц с логической связью "один ко многим"
Можно выделить несколько задач, для решения которых применяется логическая связь "один ко многим":
- Реализуется внешний ключ, ссылающийся на ту же самую таблицу (рекурсивно).
- Главная таблица используется для ограничения значений, вводимых в поле или несколько полей подчинённой таблицы.
- Связь реализуется между таблицами для повышения степени нормализации базы данных.
Отражение в сообщениях об ошибках специфики использования логической связи "один ко многим" позволяет во многих случаях повысить их информативность.
2.1. Внешний ключ, ссылающийся на собственную таблицу
В этой ситуации в таблице создается внешний ключ со ссылкой на другое поле той же самой таблицы. По этому признаку данную ситуацию можно отличить от других случаев использования внешнего ключа. Для такого типа ключей могут возникать все варианты ошибок, которые характерны для внешних ключей и описаны в разделе 2.1.3 предыдущей статьи.
Примером является внешний ключ "FK_PRD_MAIN" таблицы "PROVIDERS" (скрипт 1.2), который реализует связь между полями "PRD_ID" и "PRD_MAIN" для определения главного поставщика среди группы поставщиков. При попытке добавить запись в таблицу "PROVIDERS" и указать значение для поля "RPD_MAIN", которое не соответствует ни одному из значений поля "PRD_ID", сервер сгенерирует сообщение об ошибке с кодом -530 и текстом:
Violation of FOREIGN KEY constraint "FK_PRD_MAIN" on table "PROVIDERS". Foreign key reference target does not exist.
|
При попытке удаления записи, на которую ссылается поле "PRD_MAIN", сервер cгенерирует сообщение об ошибке с кодом -530 и текстом:
Violation of FOREIGN KEY constraint "FK_PRD_MAIN" on table "PROVIDERS". Foreign key references are present for the record.
|
Если выполнить запрос 1.4 со значением параметра "FK_PRD_MAIN", то он вернет одну запись (таблица 2.1).
Значения полей "TABLE_NAME" и "R_TABLE_NAME" запроса совпадают (таблица 2.1), что свидетельствует о том, что внешний ключ "FK_PRD_MAIN" ссылается на собственную таблицу.
2.2. Ограничения значений подчинённой таблицы
Можно выделить два варианта реализации, которые используются для этого.
1. Внешний ключ осуществляет ссылку на поля главной таблицы, которые не входят в её первичный ключ. По этому признаку ситуацию можно отличить от других случаев использования внешнего ключа.
Примером такой взаимосвязи могут быть таблицы "SALES" (скрипт 2.1) и "DISCOUNTS" (скрипт 2.2).
Скрипт 2.1. Создание таблицы "SALES".
CREATE DOMAIN D_QTY AS NUMERIC(15,3);
CREATE TABLE SALES (
SLS_ID D_INTEGER NOT NULL,
SLS_GOODS D_INTEGER NOT NULL,
SLS_QTY D_QTY NOT NULL,
SLS_DISCOUNT D_MONEY,
SLS_SUMM D_MONEY NOT NULL);
ALTER TABLE SALES ADD CONSTRAINT PK_SALES PRIMARY KEY (SLS_ID);
ALTER TABLE SALES ADD CONSTRAINT FK_SLS_GOODS
FOREIGN KEY (SLS_GOODS) REFERENCES GOODS (GDS_CODE);
CREATE GENERATOR GEN_SALES_ID;
SET TERM ^ ;
CREATE OR ALTER TRIGGER SALES_BI FOR SALES
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.sls_id is null) then
new.sls_id = gen_id(gen_sales_id,1);
end
^
SET TERM ; ^
COMMENT ON TABLE SALES IS 'Продажи';
COMMENT ON COLUMN SALES.SLS_GOODS IS 'Товар';
COMMENT ON COLUMN SALES.SLS_QTY IS 'Количество';
COMMENT ON COLUMN SALES.SLS_DISCOUNT IS 'Скидка';
COMMENT ON COLUMN SALES.SLS_SUMM IS 'Сумма';
|
Скрипт 2.2. Создание таблицы "DISCOUNTS" и внешнего ключа "FK_SLS_DISCOUNT" для таблицы "SALES" (вариант 1).
CREATE DOMAIN D_PERSENT AS NUMERIC(15,3);
CREATE TABLE DISCOUNTS (
DSC_ID D_INTEGER NOT NULL,
DSC_TITLE D_STRING100 NOT NULL,
DSC_VALUE D_PERSENT NOT NULL);
ALTER TABLE DISCOUNTS ADD CONSTRAINT UNQ_DSC_VALUE UNIQUE (DSC_VALUE);
ALTER TABLE DISCOUNTS ADD CONSTRAINT PK_DISCOUNTS PRIMARY KEY (DSC_ID);
CREATE UNIQUE INDEX IDX_DSC_TITLE ON DISCOUNTS (DSC_TITLE);
CREATE GENERATOR GEN_DISCOUNTS_ID;
SET TERM ^ ;
CREATE TRIGGER DISCOUNTS_BI FOR DISCOUNTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.dsc_id is null) then
new.dsc_id = gen_id(gen_discounts_id,1);
end
^
SET TERM ; ^
COMMENT ON TABLE DISCOUNTS IS 'Скидки';
COMMENT ON COLUMN DISCOUNTS.DSC_TITLE IS 'Название';
COMMENT ON COLUMN DISCOUNTS.DSC_VALUE IS 'Значение';
ALTER TABLE SALES ADD CONSTRAINT FK_SLS_DISCOUNT
FOREIGN KEY (SLS_DISCOUNT) REFERENCES DISCOUNTS (DSC_VALUE);
|
Для ограничения возможных значений скидок в поле "SLS_DISCOUNT" таблицы "SALES" используется внешний ключ "FK_SLS_DISCOUNT", ссылающийся на поле "DSC_VALUE" таблицы "DISCOUNTS", которое входит в уникальный ключ "UNQ_DSC_VALUE". Поле первичного ключа в этой таблице является уникальным идентификатором, значение которого определяется генератором "GEN_DISCOUNTS_ID".
2. Внешний ключ осуществляет ссылку на поля главной таблицы, входящие в её первичный ключ. Примером такой взаимосвязи может быть вариант, в котором для создания таблицы "DISCOUNTS" используется ниже приведённый скрипт 2.3.
Скрипт 2.3. Создание таблицы "DISCOUNTS" и внешнего ключа "FK_SLS_DISCOUNT" для таблицы "SALES" (вариант 2).
CREATE DOMAIN D_PERSENT AS NUMERIC(15,2);
CREATE TABLE DISCOUNTS (
DSC_VALUE D_PERSENT NOT NULL, DSC_TITLE D_STRING100 NOT NULL);
ALTER TABLE DISCOUNTS ADD CONSTRAINT PK_DSC_VALUE PRIMARY KEY (DSC_VALUE);
CREATE UNIQUE INDEX IDX_DSC_TITLE ON DISCOUNTS (DSC_TITLE);
COMMENT ON TABLE DISCOUNTS IS 'Скидки';
COMMENT ON COLUMN DISCOUNTS.DSC_VALUE IS 'Значение';
COMMENT ON COLUMN DISCOUNTS.DSC_TITLE IS 'Название';
ALTER TABLE SALES ADD CONSTRAINT FK_SLS_DISCOUNT
FOREIGN KEY (SLS_DISCOUNT) REFERENCES DISCOUNTS (DSC_VALUE);
|
В отличие от предыдущего варианта (скрипт 2.2), поле "DSC_VALUE" таблицы "DISCOUNTS" входит в её первичный ключ.
Оба варианта взаимосвязи позволяют реализовать ограничение значений поля "SLS_DISCOUNT" таблицы "SALES" значениями поля "DSC_VALUE" таблицы "DISCOUNT".
Выбор варианта, конечно же, будет определяться разработчиком БД. Если первый вариант (скрипт 2.1) позволяет выявить цель использования внешнего ключа, то при использовании второго варианта (скрипт 2.2) выявить назначение внешнего ключа без дополнительной информации практически невозможно, так как ссылка на поля первичного ключа главной таблицы используется и во многих других случаях применения внешнего ключа.
Выходом из создавшейся неопределённости может быть использование особых сообщений об ошибках, как это описано в статье [1], или хранение в БД дополнительной информации о назначении внешнего ключа.
Независимо от того, как реализована связь между таблицами (вариант 1 или 2), при возникновении ошибки для пользователя, скорее всего, будет важна информация о том, чем ограничиваются значения полей в подчинённой таблице. Поэтому в отличие от случая, описанного в разделе 1, в сообщении для пользователя во многих случаях будет необходима информация о полях главной таблицы, которые используются для ограничения значений подчинённой таблицы. Сообщение об ошибке в этом случае могут иметь, например, содержание, приведенное в разделе 2.1.3 предыдущей статьи.
2.3. Увеличение степени нормализации БД
Характерной особенностью применения внешних ключей для повышения нормализации БД является использование во внешнем ключе полей главной таблицы, составляющих её первичный ключ. Для пользователя в такой ситуации обычно важна информация о взаимосвязи записей из этих таблиц, но не о способе её реализации. Поэтому в сообщении об ошибке, как и в сообщениях для таблиц с логической связью "многие ко многим", обычно достаточно указать только таблицы, между которыми реализуется такая связь. Примером такой ситуации является взаимосвязь между таблицами "GOODS" и "SALES" (скрипты 1.1 и 2.1), реализуемая с помощью внешнего ключа "FK_SLS_GOODS".
Для получения информации о внешнем ключе может использоваться запрос 1.4. Поле "R_CONST_TYPE" этого запроса содержит информацию о том, какой ключ, первичный или уникальный, используется в главной таблице внешнего ключа для его реализации.
3. Сообщения об ошибках для таблиц с логической связью "один к одному"
Обычно такая логическая связь используется в ситуации, когда данные, которые могут быть, в общем-то, сохранены в одной записи таблицы, из-за требований проектирования БД необходимо хранить в разных таблицах. Если это вызвано необходимостью повышения производительности БД, то в подчинённой таблице обычно располагают данные, которые являются не обязательными и/или которые имеют большой размер и могут запрашиваться только при необходимости.
Подчиненная таблица в этом случае, как и в предыдущем (раздел 2.3), ссылается на поля первичного ключа главной таблицы. Дополнительным условием для подчинённой таблицы, которое определяет отношение "один к одному", является ограничение уникальности (уникальный индекс, первичный или уникальный ключ) для полей подчиненной таблицы, которые входят во внешний ключ.
Ниже приведён скрипт создания таблицы "GOODS_IMAGE", предназначенной для хранения изображений товаров. Связь таблицы "GOODS_IMAGE" с главной таблицей "GOODS" реализуется с помощью внешнего ключа "FK_GI_ID" (скрипт 3.1).
Скрипт 3.1. Создание таблицы "GOODS_IMAGE".
CREATE DOMAIN D_IMAGE AS BLOB SUB_TYPE 0 SEGMENT SIZE 80;
CREATE TABLE GOODS_IMAGE ( GI_ID D_INTEGER NOT NULL, GI_IMAGE D_IMAGE);
ALTER TABLE GOODS_IMAGE ADD CONSTRAINT PK_GOODS_IMAGE PRIMARY KEY (GI_ID);
ALTER TABLE GOODS_IMAGE ADD CONSTRAINT FK_GI_ID
FOREIGN KEY (GI_ID) REFERENCES GOODS_IMAGE (GI_ID)
ON DELETE CASCADE ON UPDATE CASCADE;
COMMENT ON TABLE GOODS_IMAGE IS 'Изображение товаров';
COMMENT ON COLUMN GOODS_IMAGE.GI_ID IS 'Товар';
COMMENT ON COLUMN GOODS_IMAGE.GI_IMAGE IS 'Изображение товара';
|
Первичный ключ таблицы "GOODS_IMAGE" используется для контроля уникальности значений её поля "GI_ID", которое, в соответствии с ограничением внешнего ключа "FK_GI_ID", может принимать только значения, совпадающие с полем первичного ключа таблицы "GOODS". Таким образом, внешний ключ "FK_GI_ID" и первичный ключ "PK_GOODS_IMAGE" реализуют между таблицами "GOODS" и "GOODS_IMAGE" логическую связь "один к одному".
Ошибки изменения данных таблицы "GOODS_IMAGE" могут быть обусловлены ограничением внешнего ключа и первичного ключа. В качестве значений правил обновления и удаления для внешнего ключа "FK_GI_ID" установлено значение "CASCADE". Поэтому внешний ключ может вызывать только ошибки при добавлении записи в таблицу "GOODS_IMAGE", если новое значение поля "GI_ID" не соответствует ни одному значению поля "GDS_ID" таблицы "GOODS".
Нарушение ограничения первичного ключа также может приводить к ошибкам уникальности, которые подробно описаны в разделе 2.1.2 статьи [1].
Для выявления логической связи "один к одному" можно использовать запросы 1.4 и 1.5.
Проблемы идентификации ошибок, вызванных ограничениями БД
При рассмотрении возможностей идентификации причины ошибок на основе анализа сообщения от сервера Firebird и структуры БД необходимо учитывать, что в некоторых случаях определение причины ошибки может оказаться затруднительным.
Во-первых, хотелось бы обратить внимание на очень ограниченную возможность использования описываемого метода на стороне сервера в хранимых процедурах и триггерах. Для анализа причины ошибки используется информация из текста сообщения об ошибке от сервера. К сожалению, в хранимых процедурах и триггерах эта информация недоступна. Сервер Firebird в хранимых процедурах и триггерах позволяет получить только код ошибки, вызванной ограничением БД. Поэтому, если при изменении данных в процедуре или триггере могут возникать ошибки с одинаковым SQLCODE, то различить их явно не удастся.
Сервер баз данных Firebird также не имеет механизмов, которые бы позволяли получать централизованно на стороне сервера информацию обо всех ошибках. Примером сервера баз данных, где такой механизм реализован, может быть Oracle Database. При возникновении ошибки или исключения в БД Oracle вызываются системные триггеры "SERVERERROR" базы данных и схемы. Кроме того, в отличие от сервера Firebird, сервер баз данных Oracle позволяет получить не только код ошибки, но и текст сообщения об ошибке. Хочется надеяться, что такая возможность появится в следующих версиях сервера Firebird.
Если рассмотреть тексты ошибок, вызванных ограничениями БД, то можно заметить, что в них во многих случаях не указывается информация о том, какая именно операция вызвала ошибку: вставка или изменение записи в таблице. Например, хотя в тексте ошибки при нарушении ограничения "NOT NULL" и указывается, что ошибка произошла при вставке записи (раздел 2.1.1 статьи [1]), но точно такое сообщение возникает и при нарушении этого ограничения при изменении записи.
Аналогична ситуация и с ошибкой, которая возникает при удалении или изменении поля главной таблицы, входящего во внешний ключ с правилом обновления "NO ACTION" (раздел 2.1.3.2 статьи [1]). Если правило обновления "NO ACTION" установлено только для одной из операций (обновления или изменения записи), то может быть полезна информация о правилах обновления внешнего ключа, полученная из системных таблиц, и на её основе может быть определена операция, при выполнении которой произошла ошибка.
Еще одна проблема была описана в начале второго раздела статьи [1]. Эта проблема связана с тем, что в тексте сообщения об ошибке, возникающей при нарушении ограничения "NOT NULL", указывается только имя поля таблицы, изменение которого вызвало ошибку. Для решения этой проблемы в статье [1] предлагается использовать уникальные имена полей таблиц в пределах всей БД.
При рассмотрении различных вариантов использования внешних ключей делалось предположение, что для реализации связи между таблицами используются внешние ключи и ограничения уникальности. В то же время нельзя забывать о том, что в ряде случаев для этого могут использоваться триггеры, реализующие контроль данных таблиц. Понятно, что в такой ситуации предположения о взаимосвязях между таблицами на основе анализа структуры БД будет сделать сложно. В такой ситуации может быть использовано несколько вариантов реализации информативных сообщений для пользователя. Эти варианты не являются взаимоисключающими и могут дополнять друг друга:
- Если взаимосвязи между таблицами реализуются с помощью триггеров, необходимые сообщения могут формироваться непосредственно в триггерах и передаваться в клиентское приложение с помощью пользовательских исключений (EXCEPTION). Например, вместо ограничения проверки CK_SALARY таблицы JOB (скрипт 2.8 статьи [1]) может использоваться проверка данных, реализуемая в триггере:
Скрипт 3.2. Создание триггера "JOB_BI0" как альтернативы ограничения проверки "CK_SALARY".
CREATE TRIGGER JOB_BI0 FOR JOB
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
begin
if (new.JOB_MIN_SALARY >= new.JOB_MAX_SALARY) then
exception E_USER_ERROR 'Для должности "' || new.job_title
|| '" максимальная заработная (' || new.JOB_MAX_SALARY
|| ') плата должна быть больше минимальной ('
|| new.JOB_MIN_SALARY || ')';
end
|
- Если часть необходимых ограничений для реализации взаимосвязи таблиц реализуется средствами БД (внешние ключи, ограничения уникальности), то в этом случае для них могут быть созданы специальные сообщения уровня БД или уровня приложения.
- Другим вариантом является сохранение информации о взаимосвязи таблиц, например, в БД в виде специальной таблицы. В этом случае сообщения об ошибках могут генерироваться на основе данных этой таблицы.
Заключение
Рассмотренные выше аспекты использования внешних ключей могут быть применимы и к другим реляционным базам данных.
Однако хочется обратить внимание на то, что, хотя эта статья и большая часть предыдущей статьи [1] были посвящены вопросу формирования сообщений на основе анализа структуры БД, не менее важным вариантом применения описанного в статьях подхода является использование специальных сообщений об ошибках уровня БД и уровня приложения. Даже если в приложении используются только специальные сообщения, то это позволит локализовать формирование сообщений в одной или нескольких процедурах, а не реализовывать формирование сообщения отдельно для каждой транзакции. Хочется заметить также, что формат специальных сообщений не обязательно должен просто текстовым. При необходимости специальные сообщения могут быть реализованы, например, в форматах HTML или RTF, содержать ссылки на разделы справочной системы или на другие информационные ресурсы, помогающие пользователю при выявлении причины ошибки и её исправлении.
Литература
- 1. Лихачев В. Н. Общий метод формирования сообщений об ошибках при работе с базами данных и его использование для БД Firebird // RSDN Magazine. - 2008. - № 4.
Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы
то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских
прав.
Copyright 1994-2016 "-"
Home
