Тема 2.5 Создание таблиц

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

Создание таблицы БД осуществляется оператором:

CREATE TABLE <имя таблицы>

[EXTERNAL [FILE “<имя файла>”] (<имя столбца>) <тип данных>]

[COMPUTED BY (выражение)]

[<домен>]

[DEFAULT <значение>]

[NOT NULL] [<ограничение>]

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

• EXTERNAL [FILE “<имя файла>”] – означает, что эта таблица будет храниться в отдельном файле.

• UNIQUE – атрибут, означающий что в столбце не может быть два одинаковых значения. Столбец, объявленный с этим атрибутом, как и первичный ключ, может применяться для обеспечения ссылочной целостности между родительской и дочерней таблицей. Для соединения с родительской таблицей в дочерней таблице строится внешний ключ.

• COMPUTED BY (выражение) – Создает вычисляемый столбец. Тип результирующего значения будет служить типом вычисляемого столбца.

Пример:

CREATE TABLE OTDEL (NOM INTEGER NOT NULL, NAIM VARCHAR(20));

Определение ключей и ссылочных целостностей.

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

CREATE TABLE OTDEL (NOM INTEGER NOT NULL PRIMARY KEY, NAIM VARCHAR(20));

Если в состав первичного ключа должны входить несколько столбцов, спецификатор ставится после определения всех столбцов.

CREATE TABLE OTDEL (NOM INTEGER NOT NULL,

NAIM VARCHAR(20) NOT NULL,

PRIMARY KEY (NOM, NAIM));

В любом случае поля, по которым строится первичный ключ, не могут быть пустыми, поэтому указывается спецификатор NOT NULL.

Внешний ключ создается для обеспечения ссылочной целостности в дочерней таблице. Формат определения внешнего ключа:

FOREIGN KEY (<список столбцов дочерней таблицы>)

REFERENCES <имя родительской таблицы>

[<список столбцов родительской таблицы>]

[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

[ON UPDATE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

В списке столбцов дочерней таблицы содержатся те поля, которые входят во внешний ключ.

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

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

• NO ACTION – при наличии подчиненных записей в дочерней таблице удаление или изменение соответствующих записей головной таблицы запрещено.

• CASCADE – при удалении записи в головной таблице происходит удаление всех подчиненных записей в дочерней таблице. При изменении записей в головной таблице изменяются значения ключевого поля во всех подчиненных ей записей дочерней таблицы.

• SET DEFAULT – при удалении или изменении записей в головной таблице ключевому полю во всех записях дочерней таблицы присваивается значение по умолчанию, указанное при определении поля; если это значение отсутствует в первичном ключе, возбуждается исключение; причем используется значение по умолчанию, имевшее место на момент определения ссылочной целостности, если впоследствии это значение будет изменено, ссылочная целостность при SET DEFAULT все равно будет использовать прежнее значение.

• SET NULL – в ключевое поле подчиненных записей дочерней таблицы заносится пустое значение NULL.

Примеры:

CREATE TABLE SOTR (ID ID_TYPE NOT NULL,

FIO VARCHAR (20), PRIMARY KEY (ID));

CREATE DOMAIN ID_TYPE AS INTEGER CHECK (VALUE>=100);

Родительская таблица:

CREATE TABLE SPR_TOVAR (TOVAR VARCHAR (20) NOT NULL COLLAGE PXW_CYRL,

ZENA INTEGER NOT NULL, PRIMARY KEY (TOVAR));

Дочерняя таблица:

CREATE TABLE PRIHOD (ID INTEGER NOT NULL PRIMARY KEY,

DATA_P DATE, TOVAR VARCHAR (20) NOT NULL COLLAGE PXW_CYRL,

FOREIGN KEY (TOVAR) REFERENCES SPR_TOVAR);

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

Ссылочная целостность может именоваться:

[CONSTRAINT <имя ссылочной целостности>]

<имя ссылочной целостности> - является не обязательным и присутствует в системных сообщениях относительно нарушения целостности и может использоваться при изменении структуры таблиц. В случае, если это имя отсутствует IB установит его сам. Для удаления непоименованной целостности придется использовать ее системное имя. Его можно узнать из системной таблицы БД с именем RDB$RELATION_CONSTRAINTS.

Пример:

Дочерняя таблица:

CREATE TABLE PRIHOD (ID INTEGER NOT NULL PRIMARY KEY,

DATA_P DATE, TOVAR VARCHAR (20) NOT NULL COLLATE PXW_CYRL,

CONSTRAINT CONS_S FOREIGN KEY (TOVAR) REFERENCES SPR_TOVAR

ON UPDATE CASCADE

ON DELETE NO ACTION);

Ограничения, накладываемые на столбцы таблицы, определяются аналогично определениям ограничений в доменах при помощи предложения CHECK(<условия поиска>).

Изменение таблицы.

Изменение таблицы осуществляется через оператор ALTER TABLE, который позволяет:

• добавить определение нового столбца;

• удалить столбец из таблицы;

• удалить атрибуты целостности таблицы или отдельного столбца;

• добавить новые атрибуты целостности.

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

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

• столбец приобретает атрибуты PRIMARY KEY или UNIQUE, но старые значения в столбце нарушают требования уникальности данных;

• удаленный столбец входил как часть в первичный или внешний ключ, что привело к нарушению ссылочной целостности между таблицами;

• столбцу были приписаны ограничения целостности на уровне таблицы;

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

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

Добавление нового столбца:

ALTER TABLE <имя таблицы> ADD < определения столбца>

Добавление нового ограничения целостности:

ALTER TABLE <имя таблицы> ADD [CONSTRAINT <имя ограничения>] <определения целостности>

Удаление столбца:

ALTER TABLE <имя таблицы> DROP <имя столбца> […,…]

Удаление ограничения целостности:

ALTER TABLE <имя таблицы> DROP <имя ограничения>

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

Для удаления таблицы целиком производится оператором

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

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