Проверка ссылочной целостности

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

Целостность данных может быть нарушена при попытке доба­вить запись в дочернюю таблицу, для которой нет соответст­вующей записи в родительской таблице. Например, в базе дан­ных имеется родительская таблица Клиенты (ИмяКлиента, Имя, Адрес, Телефон) и дочерняя таблица Продажи (ID_заказа, Товар, Цена, Количество, ИмяКлиента). В таблице Клиенты Столбец ИмяКлиента является первичным ключом (имеет уникальные и определенные значения), а в таблице Продажи столбец ИмяКлиента не обязан иметь уникальные значения, поскольку один и тот же клиент может сделать несколько различных покупок. Чтобы эти две таблицы находились в связи, столбец ИмяКлиента в таблице продажи должен быть внешним ключом, ссылающимся на пер­вичный ключ ИмяКлиента в таблице клиенты.

Если вы добавите в таблицу Продажи новую запись, содержащую значение столбца ИмяКлиента, которого еще нет в родительской таблице клиенты, то возникнет аномалия модификации данных. Аналогичная ситуация произойдет, если вы попытаетесь удалить запись из таблицы Клиенты: в дочерней таблице Продажи некото­рые записи могут ссылаться на клиентов, сведения о которых от­сутствуют.

Поэтому вначале следует добавить запись в таблицу Клиенты с соответствующим значением столбца ИмяКлиента, а затем - в продажи или, в случае с удалением, вначале нужно удалить со­ответствующие записи из таблицы Продажи, а затем - из Клиенты.

В ряде случаев перед добавлением или удалением записей можно проверить, не приведет ли это к нарушению ссылочной целост­ности. Такую проверку можно выполнить с помощью предиката MATCH.

Предикат MATCH имеет следующий синтаксис:

ЗначениеТипаЗаписи ROW MATCH [UNIQUE] [SIMPLE | PARTIAL | FOLL] (подзапрос);

Здесь в квадратных скобках указаны необязательные ключевые слова, а вертикальной чертой разделены их альтернативные ва­рианты: UNIQUE (уникальный), SIMPLE (простой), PARTIAL (частичный), FULL (полный). Эти ключевые слова определяют прави­ла обработки значений типа записи с полями, имеющими неопре­деленные значения.

Допустим, требуется определить, есть ли в таблице продажи запись ('Иванов^', 'Компьютер'). Для этого можно выполнить та­кой запрос к базе данных:

SELECT * FROM Продажи

WHERE (' Иванов ', ' Компьютер ')

MATCH (SELECT ИмяКлиента, Товар FROM Продажи);

Если в таблице Продажи есть запись с указанным именем клиента и товаром, то предикат MATCH вернет значение TRUE.

Создание и модификация таблиц

Создание и модификация таблиц базы данных обычно произво­дятся специальными диалоговыми инструментами сред разработ­ки, которые поставляются вместе с СУБД. Хотя при их использо­вании, как правило, не требуется написания программ, эти средства включают в себя языки программирования, с помощью которых можно создавать сложные приложения, ориентирован­ные на работу с базами данных. Примерами являются dBase, Paradox, Delphi, Access и др. Вместе с тем язык SQL также обла­дает средствами создания таблиц базы данных, т. е. тех таблиц, которые сохраняются в составе базы данных в долговременной памяти компьютера, например, на жестком диске. Таблицы, возвращаемые запросами на выборку данных, являются виртуальными и доступны только тому, кто инициировал эти за­просы.

Создание таблиц

Создание таблицы производится с помощью оператора CREATE TABLE (создать таблицу) с указанием необходимых параметров. При этом создается постоянная таблица. Чтобы удалить ее из ба­зы данных, требуется выполнить специальный SQL-оператор.

Для создания временной таблицы используется оператор CREATE TEMPORARY TABLE (создать временную таблицу). Временная таб­лица, в отличие от постоянной, существует только в течение сеанса работы с базой данных, в котором она была создана. Од­нако временная таблица может быть доступна другим пользова­телям, как и постоянная таблица. Обычно временные таблицы создаются для представления в них текущих итоговых (отчет­ных) данных, доступных нескольким пользователям базы дан­ных. Далее приведен синтаксис оператора CREATE:

CREATE [ TEMPORARY ] TABLE имяТаблицы (

{ Столбец1 тип [(размер)] [ограничение_столбца] [,...] }

{ [, CONSTRAINT ограничение__таблицы] [,... ] }

);

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

Для создания таблицы необходимо указать ее имя и определить столбцы. Определение столбца включает его имя и тип. Если указывается длина столбца, то она заключается в круглые скобки после типа. Кроме того, можно указать ограничения для столбца. Все перечисленные элементы определения столбца указываются друг за другом через пробел. Если создаваемая таблица содержит несколько столбцов, то их определения разделяются запятыми. Ограничение может быть определено и для всей таблицы, а не только для ее столбцов. В этом случае используется ключевое слово CONSTRAINT (ограничение), после которого указывается са­мо ограничение.

Только что созданная таблица пуста, т. е. не содержит ни одной записи.

Далее приведен запрос на создание простой таблицы без ограни­чений:

CREATE TABLE Студент (

ID _ студента    INTEGER,

ФИО        CHAR (20),

Специальность CHAR (15),

Примечание VARCHAR
 );

Данный запрос создает таблицу Студент, содержащую четыре столбца. Первый столбец целочисленный, а три других - сим­вольные.

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

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

Ограничения для столбцов

В табл. приведены основные ограничения для столбцов. Од­нако существуют и более сложные ограничения, которые здесь не рассматриваются.

Примечание: Microsoft Access не поддерживает ключевые слова DEFAULT и CHECK в определениях ограничений.

 

Рассмотрим в качестве примера создание таблицы Студент (ID_студента, ФИО, Специальность, Примечание). Идентификатор студента (целочисленный столбец ID_студента) должен одно­значно идентифицировать запись о студенте, т. е. иметь опреде­ленные и уникальные значения. Таким образом, данный столбец должен быть первичным ключом. От столбца ФИО (фамилия, имя и отчество) потребуем, чтобы в нем не было неопределенных значений. Целочисленный столбец Специальность должен со­держать номера специальностей, которые не превышают 12. Тогда запрос на создание такой таблицы будет иметь вид:

CREATE TABLE Студент (

ID _ студента   INTEGER   PRIMARY KEY,
    ФИО   CHAR(20)   NOT NULL,

Специальность INTEGER CHECK (Специальность < 12),

Примечание VARCHAR

);

Предположим, что данный запрос выполнен. Тогда следующий запроса добавление новой записи в таблицу Студент вызовет сообщение об ошибке и не будет выполнен:

INSERT INTO Студент;

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

INSERT INTO Студент (ID _ студента, ФИО, Специальность)

VALUES (1, ' Иванов Иван Иванович ', 9);

Попытаемся вслед за данным запросом добавить еще одну запись:

INSERT INTO Студент (ID __ студента, ФИО, Специальность)

VALUES (1, ' Петров Петр Петрович ', 5);

Данный запрос не будет выполнен из-за нарушения ограничения, наложенного на первый столбец: его значения должны быть не только определенными (отличными от NULL), но и уникальными. Запись можно добавить, если столбцу ID_студента присвоить, например, значение 2.

Ограничения для столбцов можно выразить и иначе - через ог­раничения доменов. Домен определяется как мно­жество значений. Домен в реляционной теории связывается с атрибутом отношения и, как таковой, определяет некоторое ограничение на этот атрибут (атрибут может прини­мать значения только из этого домена). В SQL можно создать до­мен, сначала не связанный ни с какими атрибутами (столбцами), определив для него допустимые значения: тип данных и допол­нительные ограничения. Затем этот домен можно задать в каче­стве ограничений для любого столбца в любой таблице подобно тому, как задаются типы столбцов. Другими словами, домен можно связать с одним или несколькими столбцами различных таблиц. В ряде случаев этот прием очень удобен, особенно если у вас имеется несколько "однотипных" столбцов в различных таблицах базы данных.

Чтобы создать домен, используется такой синтаксис: