Создание, редактирование и удаление таблиц

Создание таблицы заключается в задании имени и структуры таблицы и выпол­няется с помощью инструкции CREATE TABLE:

CREATE TABLE <Имя таблицы> [EXTERNAL [FILE] "<Имя файла>"]

(<Имя столбца1> <Описание столбца1> [<Ограничения столбца1>,]

…

[<Имя столбцаN> <Описание столбцаN> [ <Ограничения столбцаN>],]

[<Ограничение1 таблицы>,]

…

[ <ОграничениеN таблицы>,]

[<Описание ключа1>,]

…

[<Описание ключаN>,]

[<Описание индекса1>,]

…

[<Описание индексаN>]);

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

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

Удаление таблицы выполняется так же, как в случае локальной БД — инструкцией drop table, имеющей формат:

DROP TABLE <Имя таблицы>;

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

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

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

Как и для локальных БД, изменение таблицы выполняется инструкцией ALTER TABLE, которая позволяет добавлять и удалять отдельные столбцы, а также ограничения. В отличие от инструкции ALTER TABLE, другие инструкции типа ALTER ххх (например, alter trigger) удаляют предыдущее описание указанного объекта и заменяют его на новое.

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

1. Создать новый столбец с требуемым описанием.

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

3. Удалить старый столбец.

Рассмотрим пример, иллюстрирующий приведенную последовательность действий:

CREATE TABLE List

(Name VARCHAR(15));

ALTER TABLE List

ADD Name2 VARCHAR(20);

UPDATE List

SET Name2 = Name;

ALTER TABLE List

DROP Name;

 

Описание столбцов

Описание столбца имеет формат:

<Описание столбца> = {<Имя столбца> I COMPUTED [BY] (<Выражение>) I <Домен>}

Столбец можно определить следующими тремя способами.

1) Задать тип столбца, например, date или integer, при этом создается обыч­ный столбец указанного типа. Типы столбцов InterBase описаны в данной главе ниже.

2) Создать вычисляемый столбец, задав в операнде computed выражение.

3) Создать столбец на основе домена.

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

Пример:

CREATE TABLE List

(Name VARCHAR(20),

Price FLOAT,

Number INTEGER,

PriceAll COMPUTED BY (Price * Number));

Здесь создается таблица List, в которой ведется учет продажи товаров. Столбец Name, предназначенный для названия товара, имеет строковый тип (длина не более 20 символов). Столбец Price содержит цену единицы товара и имеет вещественный тип, а целочисленный столбец Number указывает количество товара. Столбец PriceAll содержит общую стоимость всего товара, которая рассчиты­вается как произведение цены товара на его количество. Исходя из типа опе­рандов, тип этого вычисляемого столбца автоматически будет определен как FLOAT.

Домен представляет собой именованное описание столбца. После определения домена его имя можно использовать при описании других столбцов. Домен удо­бен в случаях, когда несколько столбцов, в том числе принадлежащих различ­ным таблицам, имеют одинаковое описание. Если провести сравнение с языком Object Pascal, то аналогом домена является тип данных. Таким образом, домен можно рассматривать как шаблон описания столбца.

Перед использованием домена его нужно создать с помощью инструкции

CREATE DOMAIN <Имя домена>

[AS] <описание домена>

Имя созданного домена затем можно включать в описания столбцов:

<Имя столбца> <Имя домена>

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

Пример использования доменов:

CREATE DOMAIN D_Position AS VARCHAR(20) NOT NULL;

CREATE DOMAIN D_Price AS FLOAT CHECK(VALUE > 0);

CREATE TABLE Position

(Code INTEGER NOT NULL PRIMARY KEY,

Position D_Position,

Note VARCHAR (50ч));

CREATE TABLE Personnel

(Code INTEGER NOT NULL PRIMARY KEY,

Name VARCHAR(30),

Position D_Position,

Salary FLOAT,

BirthDay DATE);

Домен D_Position представляет собой описание строкового столбца длиной не более 20 символов, который не может быть пустым. Этот домен использован При описании столбцов Position таблиц Position И Personnel. Второй домен D_Price представляет собой числовой столбец, значение которого должно быть положительным. Это ограничение задано конструкцией check (value > 0). Для приводимых таблиц домен DPrice не нужен, а создан с целью последующего использования.

 

Ограничение столбцов

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

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

[DEFAULT {<Значение> | NULL | USER}]

[NOT NULL]

[COLLATE <Порядок сортировки>]

[CHECK <Условия>]

Операнд default определяет для столбца значение по умолчанию, которое ав­томатически заносится в столбец при добавлении к таблице новой записи.

В качестве значения по умолчанию можно указать:

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

- null — в столбец заносится нулевое значение;

- user — в столбец заносится имя текущего пользователя (для столбцов строковых типов).

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

При программировании инструкций нельзя допускать взаимоисключающие конструкции, например, одновременно указывать операнды default null и not null.

Операнд collate определяет порядок сортировки строковых значений.

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

Например:

Для таблицы Test значение столбца Name не может быть пустым, значение столбца Price должно быть положительным, а значение столбца Number — находиться в диапазоне 1..1000.

СREATE TABLE Test

(Name VARCHAR (20) NOT NULL,

Price FLOATE CHECK (Price > 0),

Namber INTEGER CHECK (Number BETWEEN 1 AND 1000));

 

Описание ключей

Для описания первичного ключа используется операнд primary key формата:

PRIMARY KEY (<Список столбцов ключа>)

Имена столбцов, образующие ключ, перечисляются в списке через запятую.

На­пример, инструкция

CREATE TABLE Personnel2

(Code INTEGER NOT NULL, Name VARCHAR(30), PRIMARY KEY (Code));

создает таблицу Personnei2, для которой по столбцу Code строится первичный ключ.

Отметим, что для ключевого столбца задан описатель not null.

В связи с тем что в таблицах InterBase отсутствует такой тип, как автоинкрементный, в обязанности разработчика входит гарантия уникальности значений ключевых столбцов. Уникальность ключа обеспечивается на программном уровне через реа­лизацию, в приложении соответствующих алгоритмов выбора. Кроме того, можно использовать генераторы.

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

UNIQUE (<Список столбцов ключа>)

Так, инструкция:

CREATE TABLE Position

(Code INTEGER NOT NULL, Position VARCHAR(20) NOT NULL, PRIMARY KEY (Code), UNIQUE (Position));

создает таблицу Position, для которой строятся два ключа — первичный ключ по столбцу code и уникальный ключ по столбцу Position.

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

 

Описание индексов

Создание и удаление индексов выполняется инструкциями create index и drop index соответственно. Инструкция создания индекса create index имеет формат:

CREATE [UNIQUE] (ASCENDING] [DESCENDING] INDEX

<Имя индекса> ON <Имя таблицы > (<Имя столбца>,..., [<Имя столбца>]);

В отличие от версии языка SQL для локальных БД, для индекса таблицы InterBase дополнительно можно задать несколько описателей:

- UNIQUE – индекс (как и ключ) требует уникальности значений столбца (столбцов), по которому он построен;

- ASCENDING - индексные столбцы сортируются в порядке возрастания значе­ний (по умолчанию);

- DESCENDING — индексные столбцы сортируются в порядке убывания значений.

Пример:

CREATE DESCENDING INDEX indNamePosition ON Personnel (Name, Position);

создает для таблицы Personnel индекс indNamePosition, построенный ПО ПО­ЛЯМ Name и Position. Для индекса устанавливается сортировка в порядке убы­вания значений.

В остальном операции с индексами для таблиц InterBase и соответствующие средства языка SQL не отличаются от ранее рассмотренных для локальных БД.

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

SHOW INDEX [<Имя таблицы>];

Эта инструкция выводит для указанной таблицы описания всех индексов, в том числе построенных по ключам. Если имя таблицы отсутствует, то выводятся описания всех индексов БД.

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

 

Ограничение таблицы

Когда вы создаёте таблицу (или, когда вы её изменяете), вы можете указывать ограничения на значения, которые могут быть введены в поля. Если вы это сделали, SQL будет отклонять любые значения, нарушающие критерии, которые вы определили.

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

CREATE TABLE <Имя таблицы> [EXTERNAL [FILE] "<Имя файла>"]

(<Имя столбца1> <Описание столбца1> [<Ограничения столбца1>,]

…

[<Имя столбцаN> <Описание столбцаN> [ <Ограничения столбцаN>],]

[<Ограничение1 таблицы>,]

…

[ <ОграничениеN таблицы>,]

[<Описание ключа1>,]

…

[<Описание ключаN>,]

[<Описание индекса1>,]

…

[<Описание индексаN>]);