Определение таблицы

Для создания таблицы используется оператор CREATE TABLE, который имеет следующий синтаксис:

CREATE TABLE [{GLOBAL|LOCAL}|TEMPORARY] имя таблицы

({определение столбца|[ограничение таблицы]}.,..

[ON COMMIT{DELETE|PRESERVE} ROWS]);

Базовые таблицы бывают постоянные или временные. Если со­здается временная таблица, то используется параметр TEMPORARY. В этом случае при определении таблицы должны быть указаны до­полнительные параметры GLOBAL или LOCAL.

Предложение ON COMMIT может использоваться только для вре­менных таблиц. Оно определяет, что происходит со строками времен­ных таблиц по завершении транзакции. По умолчанию подразумева­ется ON COMMIT DELETE ROWS.

При описании таблицы должен быть определен хотя бы один стол­бец и могут быть определены ограничения для таблицы. Столбец оп­ределяется следующим образом:

определение столбца ::=

имя столбца|{имя домена

|тип данных [размер]}

[ограничение столбца...]

[DEFAULT значение по умолчанию]

[COLLATE имя сравнения]

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

В стандарте SQL-92 принято семь типов данных, но большин­ство из них имеют подтипы с собственными именами, используе­мыми при задании типов данных для столбцов или доменов. Имен­но каждый подтип обычно и считается типом данных, однако для работы самой СУБД нет необходимости различать типы данных в пределах категорий.

Стандарт предусматривает следующие категории данных:

• CHARACTER STRING (CHARACTER, CHARACTER VARYING);

• NATIONAL CHARACTER (NATIONAL CHARACTER, NATIONAL CHARACTER VARYING);

• BIT STRING (BIT, BIT VARYING);

• EXACT NUMERIC (NUMERIC, DECIMAL, INTEGER, SMOLLINT;

• APPROXIMATE NUMERIC (FLOAT, REAL, DOUBLE PRESION);

• DATETIME (DATE, TIME, TIMESTAMP);

• INTERVAL.

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

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

Возможны следующие ограничения:

• NOT NULL;

• PRIMARY KEY;

• UNIQUE;

• CHECK;

• FOREIGN KEY/REFERENCES.

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

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

Ограничение PRIMARY KEY служит для задания первичного ключа. Как известно, ключ может быть простым, состоящим из одно­го поля, и составным, включающим несколько полей. В первом слу­чае ограничение может быть задано или непосредственно при описа­нии поля (см. пример описания таблицы «Spr_mat» ниже), или как ограничивающее условие в конце описания таблицы (см. пример опи­сания таблицы «Spr_post» ниже). Если ключ составной, то может быть использован только второй способ задания ограничения, причем поля, входящие в состав ключа поля, указываются в скобках и разделяются запятыми (см. пример описания таблицы «Post» ниже). В таблице мо­жет быть задано только одно ограничение PRIMARY KEY

Ограничение UNIQUE также может относиться как к отдельному полю, так и к совокупности полей. Оно похоже на ограничение PRIMARY KEY, но в отличие от него, во-первых, может задаваться для нескольких разных полей или совокупностей полей и, во-вто­рых, для них разрешено значение NUL.

Внешний ключ (FOREIGN KEY) - это столбец в дочерней табли­це, ссылающийся на ключ родительской таблицы. Использование вне­шних ключей является основным механизмом поддержания ссылоч­ной целостности внутри реляционной базы данных. При описании внешнего ключа указывается также ссылка на соответствующие поля в родительской таблице (REFERENCES) (см. пример описания таб­лицы «Spr_post» ниже). В родительской таблице для столбца или со­вокупности столбцов, на которые идет ссылка, должно быть задано ограничение на уникальность. Если внешний ключ составной, то при описании таблиц должны быть выполнены следующие условия: для каждого из полей, на которые идет ссылка, должно быть задано огра­ничение NOT NULL; число столбцов в ссылке на таблицу и столбцы должно соответствовать числу столбцов во внешнем ключе; i-й стол­бец во внешнем ключе соответствует i-му столбцу в ссылке на табли­цу и столбцы, тип данных и длина поля каждого столбца ссылки дол­жны соответствовать типу данных соответствующего целевого стол­бца и иметь равную длину.

При задании ограничения CHECK в скобках указывается преди­кат, который использует значения столбцов в выражении для вычис­ления значения. Предикат может принимать значения TRUE, FALSE или UNKNOWN. Ограничение считается нарушенным, когда преди­кат принимает значение FALSE.

Стандарт SQL-92 позволяет определять ограничения так, чтобы они не проверялись до завершения текущей транзакции.

Ниже приведен пример описания трех связанных таблиц: «Spr_mat» — справочник материалов, «Spr_post» — справочник постав­щиков и «Post» - таблица, содержащая информацию о поставках. При включении в базу данных информации о конкретной поставке долж­но проверяться наличие вводимых значений кода материала (kod_mat) и кода поставщика (kod_post) в соответствующих справочниках.

CREATE TABLE spr_mat

(kod_mat CHAR(3) NOT NULL PRIMARY KEY,

naim_mat CHAR (30) NOT NULL UNIQUE);

CREATE TABLE spr_post

(kod_post CHAR(5) NOT NULL,

naim_post CHAR (30) NOT NULL,

PRIMARY KEY (kod_post),

UNIQUE (naim_post));

CREATE TABLE post

(kod_post CHAR(5) NOT NULL, kod_mat CHAR(3) NOT NULL,

data_post DATE NOT NULL, kolv NUMERIC NOT NULL,

PRIMARY KEY (kodjpost, kodjnat, datajpost),

FORIGN KEY (kodjnat) REFERENCES sprjnat (kodjnat),

FORIGN KEY (kod_post) REFERENCES spr_post (kod_post));

Структуру существующей таблицы можно изменить с помощью оператора

ALTER TABLE имя таблицы

{ADD [COLUMN] определение столбца}

| {ALTER [COLUMN] <имя столбца> <изменяющее действие>}

| {DROP [COLUMN] имя столбца RESTRICT | CASCADE}

| {ADD определение ограничения для таблицы}

| {DROP CONSTAINT имя ограничения RESTRICT | CASCADE};

изменяющее действие::=

{SET DEFAULT значение по умолчанию} | {DROP DEFAULT}

Как видно, с помощью оператора ALTER TABLE можно добавить новый столбец в таблицу, изменить определение существующего стол­бца, добавив/удалив значение по умолчанию, добавить/удалить огра­ничения целостности, а также удалить столбец из таблицы. При уда­лении столбца, если будет использован параметр RESTRICT, то в слу­чае наличия каких-либо ссылок на этот столбец (в представлениях, ограничениях, условиях) оператор будет отвергнут. Если же будет ис­пользован параметр CASCADE, то все объекты, имеющие ссылки на этот столбец, будут уничтожены.

Оператор ALTER TABLE присутствует не во всех СУБД. Часто реализация этого оператора не полностью соответствует стандарту.