Таблицы. Первичные ключи и генераторы
InterBase - это реляционная СУБД. Помимо всего прочего это означает, что все данные в InterBase хранятся в виде таблиц. Таблица, как ее понимают с точки зрения SQL, очень похожа на обычную таблицу, которую можно нарисовать от руки на листе бумаги или создать в программе вроде Microsoft Excel. У таблиц в InterBase имеются столбцы и строки, в которых размещаются данные. Таблица обязательно имеет имя, уникальное в пределах одной базы данных. Таблицы являются основным хранилищем информации в базе данных, и поэтому необходимо ответственно относиться к созданию таблиц.
Существуют правила, описывающие, как создавать таблицы в реляционной базе данных, отражающие данные реального мира и в то же время позволяющие организовать эффективное хранение информации с базе данных. Процесс применения этих правил для проектирования "правильной" базы данных называется нормализаг/ией. Мы не зря взяли слово "правильной" в кавычки, потому что "нормализованная база данных" и "оптимизированная база данных" не являются синонимами. Необходимо не просто слепо следовать правилам нормализации, но и всегда делать поправку на условия конкретной задачи.
Нормализация таблиц в базе данных хорошо и подробно рассмотрена в книге [14J, и потому мы не будем пытаться объять необъятное и вернемся к нашей конкретной области - к таблицам InterBase. Рассмотрим синтаксис предложения DDL (DDL - это Data Definition Language, подробнее см. в глоссарии), которое позволяет создавать таблицы:
CREATE TABLE table [EXTERNAL [FILE] "<filespec>"]
( <col_def> [, <col_def> | <tconstraint> ...]);
Здесь table - имя создаваемой таблицы, <col_def> - описание столбцов (иногда мы будем говорить - полей) создаваемой таблицы. Опция table [EXTERNAL [FILE] "<filespec>"] означает, что будет создана так называемая внешняя таблица, которая хранится не в общем файле базы данных, а в отдельном файле с именем <filespec>.
Как видите, все просто - определяем имя таблицы и столбцы, которые в нее входят. Теперь надо подробнее рассмотреть, как определить столбцы. Синтаксис создания столбца описывается следующим предложением DDL:
<col_def> = col { datatype COMPUTED [BY] (< expr>) | domain}
[DEFAULT { literal NULL | USER}]
[NOT NULL] [ <col_constraint>]
[COLLATE collation]
Довольно большое определение, однако лишь небольшая часть приведенных в определении столбца предложений является обязательной. Каждый столбец в таблице должен иметь имя, уникальное в пределах таблицы, а также либо тип данных, определяемый предложением datatype, либо выражение <ехрг> для вычисления значения столбца (для вычисляемых столбцов), либо домен (см. ниже), определяемый domain. Типы данных были рассмотрены в главе "Типы данных", поэтому вы легко можете понять, как формируется SQL-выражение для создания таблицы.
Давайте подключимся к нашей базе данных FIRSTBASE.gdb, созданной ранее в главе "Создаем базу данных", и попробуем поработать с таблицами на практике. Для создания, удаления и модифицирования таблиц подойдет как любой из административных инструментов InterBase - из тех, что перечислены в приложении "Инструменты администратора и разработчика InterBase", так и стандартная утилита isql.exe из комплекта поставки любого клона InterBase.
Вот пример простой таблицы, названной TABLE_EXAMPLE и содержащей 3 поля различных типов:
CREATE TABLE Table_example (
ID INTEGER,
NAME VARCHAR(80),
PRICE_1 DOUBLE PRECISION);
Эта таблица иллюстрирует наиболее часто встречающийся в процессе разработки базы данных случай.
Однако возможны и другие способы определения полей. Например, мы можем задать тип поля, используя домены. Домен - это тип, определяемый пользователем для удобства применения определенных сочетаний параметров типов. Например, можно определить домен D_ID для задания полей идентификаторов. Определив домен, можно воспользоваться им для задания типа поля:
CREATE DOMAIN D_ID AS INTEGER;
CREATE TABLE Table_example (
ID D_ID,
NAME VARCHAR(80) ,
PRICE_1 DOUBLE PRECISION);
При этом поле ID будет иметь тип, определяемый доменом D_ID. Таким образом, определив в домене тип поля, необходимые проверки и ограничения, мы можем многократно применять этот домен для создания полей одинакового назначения, например денежных, без утомительного и таящего в себе опасность ошибиться переписывания определений типов переменных.
Третий способ задать столбец в таблице - это определить его как вычисляемый (COMPUTED BY) и задать условие, согласно которому будет вычисляться его значение. Например, мы можем пожелать иметь в нашей таблице столбец, вычисляющий 10% от значения поля PRICE_1. Для этого мы можем ввести следующую команду:
CREATE TABLE Table_example ( '
ID INTEGER,
NAME VARCHAR(80),
PRICE_1 DOUBLE PRECISION,
PRICE_10 COMPUTED BY (PRICE_1*0.1)) ;
Но не думайте, что теперь, как только мы вставим данные в поле PRICE_1, в поле PRICE_10 окажется десятая часть от значения этого поля. Нет, процесс тут более сложен. На самом деле мы получим искомую десятую часть только при обращении к полю PRICE_10, например при выполнении запроса SELECT
к этой таблице. То есть в вычисляемом поле не хранятся какие-либо данные, а при каждой выборке производится вычисление выражения, связанного с полем, и результат выдается в ответ на запрос.
Итак, мы рассмотрели 3 основных способа задания полей в таблице. Теперь давайте подробнее рассмотрим опции, которые можно задать при создании столбца.
Опция [DEFAULT {literal | NULL | USER}] - позволяет задать значение столбца по умолчанию. Это очень удобная возможность для автоматического заполнения данных. Существует 3 возможности задавать значения по умолчанию. Первая обозначена как literal и позволяет задавать значения по умолчанию в виде текстовых констант, чисел или даты. Например, мы можем сформировать следующие выражения для создания столбца с текстовыми значениями по умолчанию:
NAME VARCHAR(SO) DEFAULT 'Василий Станиславович1
При этом все заносимые в таблицу поля будут принимать значения по умолчанию, т. е. если не было определено другого значения для поля 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, как уже было сказано, является реляционной базой данных. Помимо всего прочего это означает, что каждая запись в таблице должна иметь некоторый признак, по которому одну запись можно отличить от другой. Для этой цели служит специальный механизм уникальных ключей.
