Мир InterBase. Архитектура, администрирование и разработка приложений баз данных в InterBase/FireBird/Yaffil

NAME VARCHAR(SO) DEFAULT 'Василий Станиславович'

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

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

PRICE_1 DOUBLE PRECISION DEFAULT NULL

И третий способ задать значение по умолчанию - это указать DEFAULT USER в определении столбца. При этом во вновь создаваемых записях в это поле будет заноситься имя текущего пользователя, т. е. пользователя, который установил соединение с InterBase и выполнил эту вставку (подробнее о пользователях см. главу "Безопасность в InterBase: пользователи, роли и права" (ч. 4)).

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

NAME VARCHAR(SO) NOT NULL

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

Но часто ограничения NOT NULL бывает недостаточно. Например, в случае хранения в базе данных каких-либо цен совершенно очевидно, что они не могут принимать отрицательные значения (хотя было бы здорово, чтобы нам приплачивали при покупке какого-нибудь товара). Чтобы заставить сервер проверять заносимые в базу данных значения цен на условие положительности, следует так определить столбец:

PRICE_1 DOUBLE PRECISION CHECK (PRICE_1>0)

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

PRICE_1 DOUBLE PRECISION NOT NULL CHECK (PRICE_1>0)

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

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

Итак, мы рассмотрели способы создания таблиц и полей с различными опциями.

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

DROP TABLE Table_example;

CREATE TABLE Table_example(ID NUMERIC(15,2);

Но такой способ изменения таблиц имеет значительные недостатки. При удалении таблицы с помощью команды DROP все данные, которые существовали в таблице, уничтожаются и, чтобы их не потерять, приходится копировать их во временные таблицы. Это весьма хлопотно. Поэтому для легкого изменения структуры таблиц существует команда ALTER TABLE, которая позволяет добавлять новые поля, удалять существующие, а также добавлять/удалять ограничения ссылочной целостности.

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

ALTER TABLE Table_example ADD Patronimic VARCHAR(SO);

После выполнения этой команды в нашей таблице Table_example появится новый столбец с именем Patronimic и типом VARCHAR(SO). А если мы пожелаем удалить из таблицы столбец с именем NAME, то следует выполнить

ALTER TABLE Table_example DROP Name;

Полный синтаксис предложения ALTER TABLE можно узнать в [1]. Это очень полезная команда, и мы будем часто ею пользоваться.

А что делать, спросите вы, если надо изменить сам столбец? Например, мы решили, что для хранения имен лучше использовать поле HUMAN_NAME, а не просто NAME. В этом случае мы можем применить команду ALTER TABLE <table> ALTER COLUMN, которая позволяет изменять наименование столбца.

Это новая команда, поддерживаемая только в InterBase 6.x и его клонах. В InterBase более ранних версий для изменения имени и любых параметров столбца пришлось бы создавать временное поле, удалять существующий столбец, создавать новый - с нужным именем и параметрами - и затем копировать данные из временного поля в новый столбец. А в 6-м InterBase это можно сделать одной командой, например такой:

ALTER TABLE Table_example

ALTER COLUMN NAME TO HUMAN_NAME;

Если же мы решили изменить тип поля, например увеличить число символов, хранимых в поле, то придется изменять домен этого поля, используя предложение ALTER DOMAIN (см. выше главу "Типы данных").

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

Первичные ключи в таблицах

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

Будем двигаться постепенно и начнем с самого распространенного типа ключей - с первичного ключа.

Итак, что же такое первичный ключ! Это одно или более полей в таблице, однозначно идентифицирующих записи в пределах этой таблицы. Звучит сложно, однако на самом деле все очень просто. Представьте себе обыкновенную таблицу, например бухгалтерскую ведомость. Что является самым первым столбцом? Правильно, порядковый номер - 1, 2, 3... Этот номер указывает на уникальную строку в пределах таблицы, и достаточно знать этот номер, чтобы найти в этой таблице строку. В данном примере это и будет первичный ключ.

Абсолютное большинство таблиц в реляционной базе данных обязательно имеют первичный ключ (часто пишут РК - сокращение от Primary key). Общей рекомендацией при создании таблиц является создавать первичный ключ. Создать первичный ключ можно как при создании таблицы, так и позже. Допустим, мы уже к моменту создания таблицы решили, что первичным ключом у нас будет поле ID Тогда добавить первичный ключ можно следующим образом:

CREATE TABLE Table_example (

ID INTEGER NOT NULL,

NAME VARCHAR(80),

PRICE_1 DOUBLE PRECISION,

CONSTRAINT pkTable PRIMARY KEY (ID));

Итак, что необходимо сделать, чтобы создать первичный ключ на таблицу table_example. Внимательно рассмотрим, что изменилось в определении таблицы? Во-первых, колонка ID получила дополнительное определение NOT NULL. Это важно - первичный ключ должен быть уникальным и не допускать неопределенных значений. A NULL, как вы знаете, это неопределенное значение. Таким образом, все поля, входящие в первичный ключ, должны иметь ограничение NOT NULL.

Чтобы завершить создание первичного ключа, в конце таблицы дописывается предложение вида CONSTRAINT <имя_ключа> <тип_ключа> (<поля_входя- щие_ в_ключ>). Полный синтаксис ограничений рассмотрен в главе "Ограничения базы данных" ч. 1, и для нашего примера первичного ключа будет иметь вид:

CONSTRAINT pkTable PRIMARY KEY (ID)

Здесь - pkTable - имя первичного ключа, a ID - столбцы, входящие в него.

Такой способ определять первичные ключи для таблиц удобен при массовом создании таблиц (например, при построении прототипа базы данных на основе скриптов, получаемых с помощью различных CASE-средств). Но что делать, если нам нужно добавить/удалить первичный ключ в таблицу, когда она уже существует и наполнена данными? Для этого следует воспользоваться еще одним расширением команды - ALTER TABLE. Пример добавления первичного ключа в нашу таблицу:

ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT FF PRIMARY KEY (ID);

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

ALTER TABLE Table_example DROP CONSTRAINT pkTable;

При этом ключ с именем pkTable будет удален из базы данных.

Генераторы - лучшие друзья первичных ключей

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

Как уже было сказано, первичный ключ может содержать несколько полей. При этом будет отслеживаться уникальность сочетания значений этих полей. Например, если мы определим ключ на поля ID и NAME, то сервер будет следить за тем, чтобы во всей таблице не было одинаковых сочетаний этих полей. То есть сочетания полей ID и NAME вроде 1 и "Иванов", 2 и "Иванов" будут корректными, поскольку они отличаются значениями поля ID.

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

Почему так? Это является отражением давнего спора между естественными и суррогатными ключами. Концепция естественных ключей утверждает, что в качестве ключа надо стараться использовать значения, реально существующие в предметной области, которую отражает база данных. Например, если мы разрабатываем систему учета людей для паспортного стола, то согласно этой концепции необходимо в качестве первичного ключа взять сочетание номера и серии паспорта. Действительно, каждый человек должен иметь свое уникальное сочетание номера и серии паспорта. Однако как быть с тем, что человек может поменять паспорт в течение жизни (в связи с достижением определенного возраста, при заключении брака и т. д.)? В этом случае нам будет необходимо сменить номер и серию паспорта, поставленные в соответствие конкретному человеку, т. е. фактически, сменить наш первичный ключ. Эго нежелательно с точки зрения разработки приложений баз данных: при разветвленной системе связей между таблицами (этому посвящена следующая глава) может понадобиться слишком много усилий разработчика для отслеживания этой ситуации.

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

Как уже было сказано, обычно первичным ключом является счетчик. Некоторые СУБД, такие, как Paradox и MS SQL, имеют специальный тип - счетчик (auto increment). При добавлении в таблицу новой записи значение поля с этим типом автоматически увеличивается на величину приращения - обычно на единицы. В InterBase нет поля типа счетчик, однако есть возможность реализовать подобное поведение. Для создания поля, которое бы заполнялось автоматически при добавлении записи в таблицу, используется совокупность средств: первым из них является генератор.

Что такое генератор! Говоря по-простому, генератор - это именованный счетчик. Внутри базы данных мы можем создать счегчик, дать ему уникальное имя в пределах этой базы и управлять значениями этого счетчика. Это и будет генератор. Чтобы это пояснить - вот пример предложений DDL:

CREATE GENERATOR gl;

SET GENERATOR gl TO 2445;

В этом примере в первой строчке в базе данных создается генератор с именем gl, а во второй - этому генератору присваивается значение 2445. Теперь возникает вопрос, как нам использовать полученный генератор. Чтобы получать и изменять значения генераторов, существует встроенная в InterBase функция GEN_ID. Эта функция принимает в качестве параметров имя генератора и величину приращения, которую нужно применить к данному генератору, а возвращает целочисленное значение, соответствующее значению генератора, полученному в результате прибавления к нему приращения. Вот пример вызова функции GEN_ID в тригере или хранимой процедуре:

Current_value = GEN_ID (gl, 1)

Чтобы получить значение генератора в клиентском приложении, можно воспользоваться таким запросом:

SELECT GEN_ID(gl, 1)FROM

RDB$ DATABASE

Так как в таблице RDB$ Database всегда содержится только одна запись, то мы получим в результате данного запроса значение генератора gl.

Здесь current_value - переменная (как использовать переменные в InterBase - см. в следующих главах), gl - генератор, 1 -приращение. В этом примере в переменную current_value попадет значение генератора gl после прибавления к нему приращения 1, т. е. следующее значение генератора!

Обратите внимание, что приращение может быть не равно единице! Более того, оно может быть даже отрицательным:

Current_value = GEN_ID (gl, -23)

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

Люди, знакомые с базами данных, могут задать вопрос: "А что будет, если одновременно несколько клиентов попробуют внести данные в одну и ту же таблицу и одновременно "дернут" генераторы? Получат ли они одно или разные значения генератора?" Однозначно, что они получат РАЗНЫЕ значения генератора. Какой бы "одновременной" ни была попытка получить значение генератора, каждый обратившийся получит свое уникальное значение. Это гарантируется самой "конструкцией" генераторов: они работают на самом низком уровне сервера и никакие процессы записи и вставки не влияют на них - часто говорят, что генераторы работают "вне контекста транзакций". Что такое транзакции, вы можете узнать в главе "Транзакции. Параметры транзакций" (ч. 1), а как устроены генераторы - в главе "Структура базы данных InterBase" (ч. 4).

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

Для этого есть два способа - вставка первичного ключа на стороне клиента и на стороне сервера. Чтобы освоить первый способ, следует обратиться к главе "Использование основных компонентов FIBPlus", а чтобы понять второй - к главе "Триггеры" (ч. 1). Здесь мы лишь кратко скажем, в чем заключается суть обоих способов.

В случае формирования первичного ключа на клиенте происходит следующее. Когда сформирована запись, которая будет вставлена в базу данных, выполняется вызов функции GEN_ID(<имя генератора>,1) и полученное значение подставляется в сформированную запись. Происходит вставка в таблицу, при этом мы получаем гарантированно уникальный первичный ключ.

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

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