Язык определения данных

Определение ограничения  таблицы осуществляется после описания всех столбцов, и при этом используется конструкция <тип_ограничения>, имеющая  следующий формат:

<ограничение_столбца>::= [CONSTRAINT имя_ограничения] {UNIQUE | PRIMARY KEY | CHECK (<условие_проверки>) | REFERENCES родительская_таблица  [(столбец)] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]}.

Рекомендуется ограничениям давать имена, т.к. при использовании  запроса ALTER TABLE удалить ограничение  можно только по его имени.

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

Если «CONSTRAINT имя_ограничения» отсутствует, то СУБД присваивает ограничению  свое системное имя. Имя ограничения  можно посмотреть в IBExpert на закладке «Ограничения» для каждого столбца  конкретной таблицы.

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

Предложение PRIMARY KEY определяет столбец в качестве первичного ключа. Следует обратить внимание на то, что  столбец таблицы, определенный как  первичный ключ, должен обязательно  иметь ограничение NOT NULL.

Рассмотрим запрос CREATE TABLE c ограничением определенного столбца. Например, чтобы создать таблицу Abonent, определив поле AccountCD в качестве первичного ключа, необходимо использовать следующий запрос:

CREATE TABLE Abonent (AccountCD VARCHAR(6) NOT NULL CONSTRAINT xpka PRIMARY KEY StreetCD INTEGER, HouseNo SMALLINT, FlatNo SMALLINT, Fio VARCHAR(20), Phone VARCHAR(15));.

Таким образом, при создании таблицы Abonent определяется ограничение  с именем xpka, указывающее в качестве первичного ключа таблицы столбец AccountCD.

Для каждого столбца можно  назначить условие проверки указанием  в ограничении столбца предложения CHECK (<условие_проверки>).

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

<условие_проверки>::= [NOT] <условие_проверки1> [[AND | OR][NOT] <условие_проверки2>]... ,

где

<условие_проверки>::= {<значение> <операция_сравнения> {<значение1> | (<скалярный_подзапрос>) | {ANY | ALL} (<подзапрос_столбца>)} | <значение> [NOT] BETWEEN <значение1> AND <значение2> | <значение> [NOT] LIKE 'шаблон' [ESCAPE 'символ пропуска'] | <значение> [NOT] CONTAINING <значение1> | <значение> [NOT] STARTING [WITH] <значение1> | <значение> [NOT] IN (<значение1> [, <значение2> ...] | <подзапрос_столбца>) | <значение> IS [NOT] NULL | <значение> IS [NOT] DISTINCT FROM <значение1> | EXISTS (<табличный_подзапрос>) | SINGULAR (<табличный_подзапрос>)},

где

<значение> ::= {столбец | константа | <выражение> | функция};

<операция_сравнения> ::= {= | < | > | <= | >= | <>} ;

<табличный_подзапрос>::= запрос select, возвращающий набор строк и столбцов;

<подзапрос_столбца>::= запрос select, возвращающий значения одного столбца, но, возможно, в нескольких строках;

<скалярный_подзапрос>::= запрос select, возвращающий значение одного столбца в одной строке.

По существу, <условие_проверки> - это не что иное, как условие  поиска предложения WHERE при использовании  вложенных запросов.

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

Например, необходимо создать  таблицу NachislSumma, определив поле NachislFactCd как первичный ключ на домене PKField. При этом начисленная сумма (поле NachislSum на домене Money) не должна быть меньше 5000, значение поля NachislYear, определяемого  на домене TYear с ограничением (VALUE BETWEEN 1990 AND 2100), должно отличаться от 1995. Запрос на создание такой таблицы будет  выглядеть следующим образом:

CREATE TABLE NachislSumma (NachislFactCD PKField NOT NULL PRIMARY KEY, AccountCD VARCHAR (30) NOT NULL, GazServiceCD PKField NOT NULL, NachislSum Money CHECK (NachislSum >= 5000), NachislMonth TMonth, NachislYear TYear CHECK (NachislYear IS DISTINCT FROM 1995));.

Если значения, помещаемые в таблицу NachislSumma, не будут удовлетворять  указанным условиям проверки, то возникнет  ошибка с SQLCODE=-297.

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

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

Предложения ON DELETE и ON UPDATE используются вместе с REFERENCES при определении внешнего ключа и предназначены для  описания типа изменения внешнего ключа  при изменении соответствующего ему значения первичного ключа. Т.е. для столбца внешнего ключа таблицы-потомка  задаются действия, автоматически выполняемые  при удалении или обновлении поля первичного ключа в таблице-родителе, на который ссылается данный внешний  ключ.

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

- NO ACTION (используется по  умолчанию) означает, что удаление  или обновление первичного ключа  родительской таблицы не изменяет  ссылающийся внешний ключ, вследствие  чего попытка операции над  родительской таблицей может  закончиться неудачей;

•  CASCADE для ON DELETE удаляет строки, содержащие значение ссылающегося внешнего ключа, а для ON UPDATE обновляет ссылающийся внешний ключ новым значением первичного ключа;

•  SET DEFAULT устанавливает значение ссылающегося внешнего ключа в заданное для него значение по умолчанию;

- SET NULL устанавливает значение  ссылающегося внешнего ключа  в NULL.

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

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

CREATE TABLE Request (RequestCD INTEGER NOT NULL PRIMARY KEY, AccountCD VARCHAR(6) REFERENCES Abonent(AccountCD) ON DELETE CASCADE ON UPDATE CASCADE, ExecutorCD INTEGER, FailureCD INTEGER, IncomingDate DATE, ExecutionDate DATE, Executed SMALLINT);.

4.2.3. Определение  ограничений на таблицу

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

<тип_ограничения> ::= [CONSTRAINT имя_ограничения] {{PRIMARY KEY | UNIQUE} (<список_столбцов>) | FOREIGN KEY (<список_столбцов>) REFERENCES родительская_таблица [(столбец1 [, столбец2 ...])] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] | CHECK (<условие_проверки>)} [USING [ASC[ENDING] | DESC[ENDING]] INDEX имя_индекса].

Существует три вида ограничений базовой таблицы:

- ограничение первичного  ключа; 

-  ссылочное ограничение (ограничение внешнего ключа);

-  ограничение "проверочного условия".

При задании ограничения  таблицы можно определить первичный  ключ (как простой, так и составной). Если первичный ключ не является составным, то его можно определить при задании  ограничения столбца (что было рассмотрено  выше), но составной первичный ключ можно определить только с помощью ограничения таблицы. Для этого поля таблицы, являющиеся составным первичным ключом, перечисляются в скобках через запятую в предложении PRIMARY KEY.

Аналогично при задании  ограничения таблицы можно определить как простой, так и составной  внешний ключ, причем если простой  ключ уже был определен в ограничении  столбца, то определять его снова  не требуется. Составной внешний  ключ можно определить только с помощью  ограничения таблицы, и для этого  используется предложение FOREIGN KEY со списком  столбцов и предложением REFERENCES c именами  содержащих их таблиц, на которые ссылается  данный внешний ключ.

Использование параметров NO ACTION, CASCADE, SET DEFAULT и SET NULL в предложениях ON DELETE и ON UPDATE аналогично использованию  их при определении ограничения  столбца (было рассмотрено выше).

Таким образом, если определяется простой ключ (первичный или внешний), то его можно задать как в ограничении  столбца, так и в ограничении  таблицы - результат будет одинаков. Однако если ключ составной, то его  можно определить только в ограничении  на таблицу.

Например, чтобы при создании таблицы NachislSumma определить составной  первичный ключ, состоящий из полей NachislFactCD и NachislYear, и внешние ключи AccountCD и GazServiceCD, необходимо применить  следующий запрос:

CREATE TABLE NachislSumma (NachislFactCD INTEGER NOT NULL, NachislSum NUMERIC(15,2), NachislYear SMALLINT NOT NULL, NachislMonth SMALLINT, AccountCD VARCHAR(6) NOT NULL, GazServiceCD INTEGER NOT NULL, PRIMARY KEY (NachislFactCD, NachislYear), FOREIGN KEY (AccountCd) REFERENCES Abonent(AccountCD), FOREIGN KEY (GazServiceCD) REFERENCES Services(GazServiceCD));.

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

При использовании предложения UNIQUE накладывается условие уникальности на значения столбцов таблицы, перечисленных  в скобках через запятую. В  столбцах с уникальными ограничениями  можно использовать NULL-значения. Следует  учесть, что если в столбцы, на значения которых наложено условие уникальности, пытаться ввести только NULL значения, то они не будут считаться одинаковыми  и команда будет выполнена  успешно.

Создадим, например, таблицу Phone_Sprav с четырьмя столбцами: столбец number типа INTEGER, столбец Fio типа VARCHAR (25), столбец Home_Phone типа VARCHAR (6) и столбец Mobil_Phone типа VARCHAR (11). При этом столбец number определим  в качестве первичного ключа, а в  качестве ограничения на таблицу  определим уникальность столбцов Home_Phone и Mobil_Phone. Запрос CREATE TABLE для создания таблицы Phone_Sprav примет следующий вид:

CREATE TABLE Phone_Sprav (number INTEGER PRIMARY KEY, Fio VARCHAR(25), Home_Phone VARCHAR(6), Mobil_Phone VARCHAR(11), UNIQUE (Home_Phone, Mobil_Phone));.

Если попытаться добавить в таблицу строки (позже будет  рассмотрено, как это можно сделать), в которых значения полей number и Fio различны, а поля Home_Phone и Mobil_Phone равны NULL, то вставка этих данных пройдет  успешно, так как считается, что  каждое (NULL, NULL) в уникальных столбцах (Home_Phone, Mobil_Phone) отличается от любого другого (NULL, NULL). Однако попытка вставить строки с одинаковыми значениями в одном из уникальных столбцов (Home_Phone или Mobil_Phone) и с NULL значениями в другом не удастся.

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

Ограничение

CHECK (<условие_проверки>)

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

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

CREATE TABLE Request (RequestCD INTEGER NOT NULL PRIMARY KEY, AccountCD VARCHAR(6) REFERENCES Abonent(AccountCD) ON DELETE CASCADE ON UPDATE CASCADE, ExecutorCD INTEGER, FailureCD INTEGER, IncomingDate DATE, ExecutionDate DATE, Executed SMALLINT, CONSTRAINT ExecDat CHECK (ExecutionDate BETWEEN IncomingDate AND IncomingDate+7));.

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

Предложение

[USING [ASC [ENDING] | DESC [ENDING]] INDEX имя_индекса]

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

4.2.4. Удаление таблицы  БД

Для удаления существующей таблицы используется запрос DROP TABLE, который имеет следующий формат:

DROP TABLE базовая_таблица;.

Следует отметить, что нельзя удалить таблицу в следующих  случаях:

•  если на ее столбцы ссылаются внешние ключи других таблиц;

•  если она используется другими объектами БД (например, представлением);

•  если она определена в текущей транзакции, на момент удаления еще не завершенной.

При попытке удалить используемую таблицу выдается сообщение об ошибке c SQLCODE=-607 и сообщением, что удаляемый  объект еще находится в использовании.

4.2.5. Изменение  определения таблицы

Созданную запросом CREATE TABLE базовую таблицу можно изменить запросом ALTER TABLE. Он поддерживает следующие  изменения:

•  добавление новых столбцов;

•  задание нового ограничения целостности для базовой таблицы;

•  определение нового имени для существующего столбца;

•  изменение типа данных для существующего столбца;

•  изменения порядкового номера столбца в таблице;

•  определение для существующего столбца нового значения по умолчанию (заменяющего предыдущее значение, если оно было);

•  удаление для столбца существующего значения по умолчанию;

•  удаление существующего столбца;

•  удаление существующего ограничения целостности для базовой таблицы.

Запрос ALTER TABLE имеет следующий  формат:

ALTER TABLE базовая_таблица <действие1> [, <действие2> ...];,

где базовая_таблица - это  имя существующей базовой таблицы  БД, определение которой требуется  изменить;

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

<действие> ::= {ADD <определение_столбца> | ADD <тип_ограничения> | ALTER [COLUMN] столбец <изменение> | DROP столбец | DROP CONSTRAINT имя_ограничения},