5. Поддержка целостности

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

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

Оператор создания таблицы на языке SQL выглядит следующим образом:

CREATE TABLE <имя таблицы> (<имя столбца 1><тип данных>[(<размер>)] [<ОГРАНИЧЕНИЯ НА СТОЛБЕЦ 1>][,<имя столбца 2><тип данных> [(<размер>)][<ОГРАНИЧЕНИЯ НА СТОЛБЕЦ 2>]...] [<ОГРАНИЧЕНИЯ НА ТАБЛИЦУ>[(СПИСОК СТОЛБЦОВ)]])

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

Ниже описаны основные ограничения, предусмотренные языком SQL. В конкретных реализациях СУБД этот набор может быть расширен.

Ограничение NOT NULL предотвращает появление в таблице незаполненных полей.

CREATE TABLE T1 (F1 INTEGER NOT NULL, F2 CHAR (8) NOT NULL, F3 CHAR (20))

В поля F1 и F2 значения должны вводиться обязательно, в поле F3 ввод не обязателен, допускается отсутствие значения, которое трактуется как «не введено», «неизвестно».

Можно также потребовать, чтобы поле или группа полей содержала только уникальные значения. Уникальность может сочетаться с запретом NULL-значений.

CREATE TABLE T2 (F1 INTEGER NOT NULL UNIQUE, F2 CHAR (8) NOT NULL, F3 CHAR (20), F4 INTEGER, UNIQUE (F3, F4))

Поле F1 должно иметь только уникальные, обязательно вводимые значения, в поле F2 значения обязательно должны вводиться, но могут быть неуникальными, в полях F3 и F4 допускаются любые значения, в том числе неуникальные и NULL, но сочетание значений этих полей, находящихся в одной строке, должно быть уникальным. Этот тип ограничений относится к ограничениям на таблицу.

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

CREATE TABLE T3 (F1 INTEGER PRIMARY KEY, F2 CHAR (8) NOT NULL, F3 CHAR ( 20 ))

CREATE TABLE T4 (F1 INTEGER NOT NULL, F2 CHAR ( 8 ) NOT NULL, F3 CHAR ( 20 ), PRIMARY KEY ( T1, T2 ) )

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

Любой неключевой атрибут может иметь ограничения NOT NULL и UNIQUE. Возможно произвольное количество таких атрибутов в отношении. Первичный ключ, обладающий теми же свойствами, может быть только один. Он имеет особый статус как на логическом уровне, так и на физическом. По ключу СУБД автоматически, без команды проектировщика базы данных строит индекс. Для индексирования неключевых атрибутов нужна специальная команда.

Ограничения целостности могут также включать проверку вводимых значений с помощью логического выражения. Допускаются к вводу только значения, при которых логическое выражение истинно. В противном случае СУБД сообщает об ошибке. Требование проверки логического выражения обозначается в SQL-команде ключевым словом CHECK. Проверка логических выражений может произвольным образом сочетаться с NOT NULL и UNIQUE.

CREATE TABLE T5 (F1 INTEGER NOT NULL UNIQUE, F2 DECIMAL CHECK ( F2 < 1 ), F3 CHAR(10) CHECK (F3 IN ( ‘PARIS’, ‘BREMEN’,‘MADRID’ ) ), F4 CHAR ( 15 ) CHECK ( F4 BETWEEN ‘AA’ AND ‘QZ’ ), F5 DECIMAL, F6 CHAR ( 20 ), CHECK (F5 < 29 OR F6 = ‘LONDON’ ) )

Логическое выражение может предусматривать следующие виды проверок: простое сравнение (поле F2), принадлежность множеству (поле F3), принадлежность диапазону значений, включая граничные (поле F4). Эти проверки относятся к соответствующему столбцу. Последняя проверка затрагивает несколько полей (F5 и F6) и является ограничением на таблицу.

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

CREATE TABLE T11 (F1 INTEGER PRIMARY KEY, F2 CHAR ( 8 ), F3 INTEGER, FOREIGN KEY ( F3 ) REFERENCES T1 ( F25 ) )

Ссылка на главную таблицу оформляется как ограничение на таблицу. Ключевые слова FOREIGN KEY указывают поля подчиненной таблицы, получающие значения из внешнего источника. Ключевое слово REFERENCES указывают источник ссылок (главную таблицу и ее поля). Внешний ключ может быть многостолбцовым. Синтаксически это оформляется следующим образом:

... FOREIGN KEY (< список столбцов подчиненной таблицы >) REFERENCES< имя родительской таблицы > (< список столбцов родительской таблицы>)

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

... F3 INTEGER REFERENCES T1 ( F25 )

Определение поля F3 как внешнего ключа подразумевается.

Поле связи в главной таблице обычно является первичным ключом. В этом случае его можно не указывать:

... F3 INTEGER REFERENCES T1

Допускается формирование ссылок типа «сам на себя», т.е. источником внешнего ключа может быть некоторое поле той же таблицы:

CREATE TABLE EMP ( EMPNO INTEGER PRIMARY KEY, NAME CHAR ( 20 ) NOT NULL, MANAGER INTEGER REFERENCES EMP)

В данном примере в поле MANAGER допускаются только значения из поля EMPNO. Таблица отражает должностную подчиненность: первым руководителем является ROBERTS, ему подчиняются SMITH и CLINTON, в подчинении у SMITH находится BAKER.

EMPNO	NAME	MANAGER
1003	SMITH	2007
2007	ROBERTS	NULL
1688	BAKER	1003
1234	CLINTON	2007

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

Допустимы следующие варианты определения ссылочной целостности:

1. CASCADE – каскадное изменение/удаление. Удаление записи в главной таблице приводит к удалению записей с соответствующим значением внешнего ключа. Например, при удалении записи о заказчике удаляется вся информация о его заказах. При изменении идентификатора заказчика аналогичные изменения делаются в таблице заказов, чтобы сохранить сведения о принадлежности заказов конкретному заказчику;

2. SET NULL – сброс внешнего ключа в NULL. При увольнении (операция удаления) или переводе (операция корректировки) сотрудника поле «Исполнитель» таблицы заказов очищается, что означает, что исполнитель данного заказа не назначен;

3. SET DEFAULT – сброс внешнего ключа в значение по умолчанию. Вместо значения NULL предыдущего случая поле внешнего ключа заполняется ранее заданным значением по умолчанию;

4. NO ACTION – запрет изменений при нарушении ссылочной целостности. СУБД отказывает в выполнении операции и выдает сообщение о нарушении ссылочной целостности. Например, нельзя удалить запись о сотруднике или перевести его в другой отдел, если за ним закреплены неисполненные заказы.

Синтаксически ограничения ссылочной целостности оформляются следующим образом:

... [ ON UPDATE { CASCADE | SET NULL | SET DEFAULT | NO ACTION } ] [ ON DELETE { CASCADE | SET NULL | SET DEFAULT | NO ACTION } ]

Пусть имеются таблицы заказчиков Customers, разработчиков Designers и заказов Orders. Межтабличные связи приведены на рис. 6.1.

Рис. 6.1. Межтабличные связи

Бизнес правила:

1. Изменение поля связи Custnum в таблице заказчиков или удаление записи о заказчике каскадно отражается в таблице заказов;

2. Изменение поля связи Desnum в таблице разработчиков каскадно отражается в таблице заказов. При удалении записи о разработчике соответствующие поле таблицы заказов получает значение NULL.

Оператор SQL для создания таблицы заказов:

CREATE TABLE ORDERS (ORDNUM INTEGER NOT NULL PRIMARY KEY, AMT DECIMAL, ORDDATE DATE NOT NULL, CUSTNUM INTEGER NOT NULL REFERENCES CUSTOMERS ON UPDATE CASCADE ON DELETE CASCADE, SALNUM INTEGER REFERENCES SALPEOPLE ON UPDATE CASCADE ON DELETE SET NULL )

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

В некоторых реализациях СУБД можно также определить поведение связки главный-подчиненный при операциях удаления и корректировки подчиненных таблиц.

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

CREATE [ UNIQUE ] INDEX < имя индекса > ON < имя таблицы > ( < имя столбца j > [ , < имя столбца k > ...] )

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

…<имя столбца><тип данных>[(<размер>)] [DEFAULT = <ЗНАЧЕНИЕ ПО УМОЛЧАНИЮ>] [<ОГРАНИЧЕНИЯ НА СТОЛБЕЦ>]

Пример:

… CITY CHAR ( 20 ) DEFAULT = ‘LONDON’, …