Таблицы. Первичные ключи и генераторы
NOTICE: Этот документ является главой из книги «Мир InterBase», написанной Алексеем Ковязиным и Сергом Востриковым.
InterBase - это реляционная СУБД. Кроме того, это означает, что все данные в InterBase хранятся в виде таблиц. Таблица, как она реализована с точки зрения SQL, очень похожа на обычную таблицу, которую можно нарисовать от руки на листе бумаги или создать в такой программе, как Microsoft Excel. Таблицы в InterBase имеют столбцы и строки, в которых размещаются данные. Таблица обязательно имеет имя, уникальное в пределах одной базы данных. Таблицы являются основным хранилищем информации в базе данных, и поэтому при создании таблиц следует быть очень внимательным.
Существуют правила, описывающие, как создавать таблицы в реляционной базе данных, отражающие данные реального мира и в то же время позволяющие организовать эффективное хранение информации в базе данных. Процесс применения этих правил для проектирования «правильной» базы данных называется нормализацией. Мы сознательно взяли слово «правильной» в кавычки, потому что «нормализованная база данных» и «оптимизированная база данных» - не синонимы. Вам не обязательно однозначно следовать правилам нормализации - всегда вносите поправку на специфику конкретной задачи.
Нормализация таблиц в базе данных подробно рассматривается в книге [14. и поэтому мы не будем пытаться объять необъятное и вернемся к нашей теме обсуждения - таблицам InterBase. Рассмотрим синтаксис предложения DDL (DDL - язык определения данных, подробнее см. глоссарий), которое позволяет создавать таблицы:
CREATE TABLE table [EXTERNAL [FILE] «»] ( [, | …]);
Здесь table - имя создаваемой таблицы, - описание столбцов (иногда мы будем говорить - полей) создаваемой таблицы. Опция table [EXTERNAL [FILE] «»] означает, что будет создана так называемая внешняя таблица, которая хранится не в общем файле базы данных, а в отдельном файле с именем . Как видите, все просто - мы определяем имя таблицы и столбцы, которые она содержит. Теперь подробно рассмотрим, как определить столбцы. Синтаксис создания столбца описывается следующим предложением DDL:
= col { datatype | COMPUTED [BY] (< expr>)} | domain}
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [ ]
[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 Тable_example ( ID D_ID, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Поле ID будет иметь тип, определенный доменом D_ID. Таким образом, определив тип поля в домене, необходимые проверки и ограничения, мы можем применять этот домен многократно для создания полей одинакового назначения. Например, денежных, без утомительного и таящего опасность ошибиться копирования определений типов переменных. Третий способ задать столбец в таблице - определить его как вычисляемый (COMPUTED BY) и указать условие, по которому будет вычисляться его значение. Например, мы можем захотеть иметь в нашей таблице столбец, вычисляющий 10 % от значения поля PRICE_1. В этом случае следует написать следующую команду:
CREATE TABLE Тable_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(80) DEFAULT ‘Василий Станиславович’
Таким образом, все поля, вставленные в таблицу, примут значения по умолчанию, т.е. если для поля NAME не было определено другое значение, появится строка ‘Василий Станиславович’. Второй способ задать значение по умолчанию - указать DEFAULT NULL в определении столбца. И во вновь создаваемых записях значение этого столбца будет NULL, если другое значение, конечно, не было явно задано. Пример:
PRICE_1 DOUBLE PRECISION DEFAULT NULL
Третий способ задать значение по умолчанию - указать DEFAULT USER в определении столбца. Таким образом, во вновь создаваемых записях это поле будет содержать имя текущего пользователя, т.е. пользователя, который установил соединение с InterBase и выполнил эту вставку (подробнее о пользователях см. главу «Безопасность в InterBase: пользователи, их функции и права» (часть 4)). Для некоторых полей важно, чтобы поле имело непустое значение. Например, поле, которое не может быть пустым согласно спецификации задачи. Чтобы установить на уровне базы данных ограничение, что поле должно иметь определенное значение, необходимо внести следующее дополнение в описание столбца:
NAME VARCHAR(80) NOT 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(80);
После выполнения этой команды наша таблица Table_example будет иметь новую колонку с именем Patronimic и типом VARCHAR (80). Если мы хотим удалить колонку с именем NAME из таблицы, следует выполнить следующее:
ALTER TABLE Table_example DROP Name;
Полный синтаксис оператора ALTER TABLE вы можете увидеть в [1.. Это очень полезная команда, и мы будем часто её использовать.
А что делать, спросите вы, если необходимо изменить колонку? Например, мы решили, что для хранения имён лучше использовать поле HUMAN_NAME, чем NAME. В этом случае мы можем применить ALTER TABLE
ALTER TABLE Table_example ALTER COLUMN NAME TO HUMAN_NAME;
Если мы решили изменить тип поля, например, увеличить количество символов, хранящихся в поле, нам придётся изменить домен этого поля с помощью оператора ALTER DOMAIN (см. главу «Типы данных» выше).
Итак, мы рассмотрели создание и изменение таблиц в InterBase. Теперь пора немного углубиться в теорию баз данных. InterBase, как уже было сказано, является реляционной базой данных. Кроме того, это означает, что каждая запись в таблице должна иметь характеристику, по которой одну запись можно отличить от другой. Для этой цели служит специальный механизм уникальных ключей.
Первичные ключи в таблицах
Конечно, мы можем создать таблицу, которая не содержит никаких ключей. Нам это не запрещено. Но, как было сказано ранее, создание эффективной базы данных невозможно без соблюдения правил нормализации. Наличие ключей является важнейшим элементом нормализации. Поэтому, хотя мы и не ставим целью рассмотрение теории и нормализации баз данных, мы должны ввести определение ключей и рассмотреть их функцию в InterBase. Мы будем двигаться шаг за шагом и начнём с самого распространённого типа ключей - первичного ключа.
Итак, что такое первичный ключ? Это одно или несколько полей в таблице, однозначно идентифицирующих записи в этой таблице. Звучит сложно, однако на самом деле всё очень просто. Представьте обычную таблицу, например, бухгалтерский лист. Что находится в самой первой колонке? Правильно, порядковый номер - 1, 2, 3 … Этот номер обозначает уникальную строку в таблице, и достаточно знать этот номер, чтобы найти строку в этой таблице. В этом примере это будет первичный ключ. Подавляющее большинство таблиц в реляционной базе данных обязательно имеет первичный ключ (PK - аббревиатура от 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. Это важно, потому что первичный ключ должен быть уникальным и не допускать неопределённых значений. А NULL, как вы знаете, является неопределённым значением. Таким образом, все поля, включённые в первичный ключ, должны иметь ограничение NOT NULL. Для завершения создания первичного ключа в конце таблицы следует написать: CONSTRAINT ()
Полный синтаксис ограничений вы можете найти в главе «Ограничения базы данных» þ. 1, а для нашего примера первичного ключа это будет выглядеть так:
CONSTRAINT pkTable PRIMARY KEY (ID)
Здесь pkTable - это имя первичного ключа, а 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 и 1 и «Иванов», 2 и «Иванов» будут корректными, так как они различаются значениями поля ID.
Таким образом, первичный ключ может включать несколько полей любых типов. Однако на практике наиболее распространённым типом ключа является счётчик - целочисленное поле, которое содержит возрастающие значения. Почему это так? Это отражение старого спора между естественными и суррогатными ключами. Концепция естественных ключей говорит, что в качестве ключа мы должны пытаться использовать значения, реально существующие в предметной области, которую отражает база данных. Например, если мы разрабатываем систему регистрации людей для паспортного стола, согласно этой концепции в качестве первичного ключа следует взять комбинацию номера и серии паспорта. Действительно, каждый человек должен иметь уникальную комбинацию номера и серии паспорта. Однако что делать с тем фактом, что человек может сменить паспорт в течение своей жизни (в связи с достижением определённого возраста, вступлением в брак и т.д.)? В этом случае нам придётся изменить номер и серию паспорта, установленные в соответствии с конкретным человеком, то есть фактически изменить наш первичный ключ. Это нежелательно с точки зрения разработки приложений баз данных: учитывая разветвлённую систему связей между таблицами (следующая глава посвящена этому), разработчику придётся приложить большие усилия для контроля этой ситуации.
Поэтому в большинстве случаев используется замещающий ключ. Замещающий - значит искусственный, то есть не существующий в предметной области, которую описывает наша база данных, а созданный искусственно - для удобства разработки приложений баз данных. Как уже было сказано, обычно в качестве первичного ключа используется счетчик. В некоторых СУБД, таких как Paradox и MS SQL, существует специальный тип - счетчик (автоинкремент). При добавлении новой записи в таблицу значение поля этого типа автоматически увеличивается на величину инкремента - обычно на единицу. В InterBase нет поля типа счетчик, однако такое поведение можно реализовать. Для создания поля, которое автоматически заполнялось бы при добавлении записи в таблицу, используется набор ресурсов: первый из них - генератор.
Что такое генератор? Если говорить просто, генератор - это именованный счетчик. В рамках базы данных мы можем создать счетчик, дать ему уникальное имя в пределах этой базы и управлять значениями этого счетчика. Это и будет генератор. Вот пример операторов DDL, который вам это пояснит:
CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;
В первой строке этого примера создается генератор с именем g1, а во второй строке этому генератору присваивается значение 2445. Теперь возникает вопрос, как использовать полученный генератор. В InterBase есть встроенная функция GEN_ID для получения и изменения значений генераторов. Эта функция принимает в качестве параметров имя генератора и значение инкремента, которое должно быть применено к данному генератору, и возвращает целочисленное значение, соответствующее значению генератора, полученному в результате добавления к нему инкремента. Вот пример вызова функции GEN_ID в триггере или хранимой процедуре:
Current_value = GEN_ID (g1, 1)
Если мы хотим получить значение генератора, можно использовать следующий запрос:
SELECT GEN_ID(g1, 1)FROM RDB$ DATABASE
Поскольку таблица RDB $ Database всегда содержит только одну запись, в результате данного запроса мы получим значение генератора g1.
Здесь current_value - это переменная (в следующих главах вы найдете информацию о том, как использовать переменные в InterBase), g1 - генератор, 1 - инкремент. В этом примере значение генератора g1 попадет в переменную current_value после добавления к нему инкремента 1, то есть следующее значение генератора. Обратите внимание, что инкремент не обязательно должен быть равен 1! Более того, он может быть даже отрицательным: Current_value = GEN_ID (g1, -23)
В результате выполнения этой функции текущее значение генератора g1 будет уменьшено на 23. Как видите, диапазон возможных применений генераторов довольно широк - его можно использовать не только для получения значений первичных ключей, но и для отслеживания глобальных изменений в базе данных.
Люди, знакомые с базами данных, могут задать вопрос: «Что произойдет, если одновременно несколько клиентов попытаются вставить данные в одну и ту же таблицу и одновременно “потянут” генераторы? Получат ли они одинаковые или разные значения генератора?». Они однозначно получат РАЗНЫЕ значения генератора. Насколько бы «одновременной» ни была попытка получить значение генератора, каждый обратившийся получит уникальное значение. Это гарантировано «конструкцией» генераторов: они работают на самом низком уровне сервера, и никакие процессы записи и вставки на них не влияют - часто говорят, что генераторы работают «вне контекста транзакций». Если вы хотите узнать о транзакциях, прочитайте главу «Транзакции. Параметры транзакций» (часть 1); о том, как устроены генераторы - «Структура базы данных InterBase» (часть 4). Итак, со стороны генераторов мы имеем надежный механизм для создания уникальных первичных ключей. Однако можем ли мы использовать этот механизм? Как поместить значение, полученное от генератора, в поле первичного ключа?
Для этого есть два способа - вставить первичный ключ от имени клиента и от имени сервера. Чтобы освоить первый способ, следует обратиться к главе «Использование основных компонентов FIBPlus», а чтобы понять второй - к главе «Триггеры» (часть 1). Здесь мы кратко рассмотрим основную суть обоих способов.
В случае создания первичного ключа от имени клиента происходит следующее. Когда формируется запись, которая будет вставлена в базу данных, выполняется вызов функции GEN_ID (, 1), и полученное значение подставляется в эту запись. Затем происходит вставка в таблицу, и мы гарантированно получаем уникальный первичный ключ.
Второй способ - создание первичного ключа от имени сервера - в целом избавляет клиента от всякой заботы о том, каким будет значение первичного ключа. В этом случае при вставке записи срабатывает триггер - специальный объект базы данных, который может выполнять любые операции при вставке/удалении/обновлении записей в таблицах. И в этом триггере выполняются следующие операции: вызов функции GEN_ID, получение требуемого значения генератора и его вставка в таблицу. Преимущество второго способа в том, что при разработке клиентского приложения вообще не нужно беспокоиться о создании первичного ключа, единственное, что нужно сделать, - это один раз написать необходимый триггер. Но недостаток в том, что мы не можем получить значение сгенерированного ключа в приложении сразу после вставки! Если мы используем первый способ, мы можем получить значение первичного ключа, хотя нам придется заботиться о его создании при каждой вставке. Трудно сказать наверняка, какой способ лучше, все зависит от конкретной задачи. Далее в этой книге мы рассмотрим возможные варианты решения вопросов, связанных с работой с первичным ключом.
Заключение
Итак, в этой главе мы рассмотрели, как создавать и обновлять таблицы в InterBase, а также управлять первичными ключами. Таким образом, мы рассмотрели основные объекты в InterBase, которые условно можно назвать статическими, поскольку они только хранят информацию и не выполняют ее преобразование. Далее мы поговорим о способах управления информацией и преобразования информации в базе данных.