FirebirdSQL logo
 DOMAININDEX 

Внешние таблицы

Необязательное предложение EXTERNAL [FILE] указывает, что таблица хранится вне базы данных во внешнем текстовом файле.Столбцы таблицы, хранящейся во внешнем файле, могут быть любого типа за исключением BLOB и массивов с любым типом данных.

Над таблицей, хранящейся во внешнем файле, допустимы только операции добавления новых строк (INSERT) и выборки (SELECT) данных.Операции же изменения существующих данных (UPDATE) или удаления строк такой таблицы (DELETE) не могут быть выполнены.

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

Файл с внешней таблицей должен располагаться на устройстве хранения, физически расположенном на сервере, на котором расположена СУБД.Если параметр ExternalFileAccess в файле конфигурации firebird.conf содержит Restrict, то файл внешней таблицы должен находится в одном из каталогов, указанных в качестве аргумента Restrict.Если при обращении к таблице Firebird не находит файла, то он создаёт его при первом обращении.

Important

Возможность использования для таблиц внешних файлов зависит от установки значения параметра ExternalFileAccess в файле конфигурации firebird.conf:

  • Если он установлен в значение None, то запрещён любой доступ к внешнему файлу.

  • Значение Restrict рекомендуется для ограничения доступа к внешним файлам только каталогами, созданными специально для этой цели администратором сервера. Например:

    • ExternalFileAccess = Restrict externalfiles ограничит доступ директорией externalfiles корневого каталога Firebird.

    • ExternalFileAccess = Restrict d:\databases\outfiles; e:\infiles ограничит доступ только двумя директориями Windows. Обратите внимание, что любые пути являющиеся отображением сетевых путей не будут работать. Также не будут работать пути заключённые в одинарные или двойные кавычки.

  • Значение Full позволяет доступ к внешним файлам в любом месте файловой системы хоста. Это создаёт уязвимость и не рекомендуется к использованию.

Формат внешних файлов

Внешняя таблица имеет формат “строк” с фиксированной длинной.Нет никаких разделителей полей: границы полей и строк определяются максимальными размерами в байтах в определении каждого поля.Это необходимо помнить и при определении структуры внешней таблицы, и при проектировании входного файла для внешней таблицы, в которую должны импортироваться данные из другого приложения.Например, широко распространённый формат “.csv”, не может быть использован в качестве входного файла, и не может быть получен непосредственно как внешний файл.

Самым полезным типом данных для столбцов внешних таблиц является тип CHAR с фиксированной длинной, длинна должна подходить под данные с которыми необходимо работать.Числовые типы и даты легко преобразуются в них, строки получаются как есть, в то время как, если данные не читаются другой базой данных Firebird, то родные типы могут быть нераспознаваемыми дня внешних приложений и являться для них “абракадаброй”.

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

Разделитель строк

Как правило, внешние файлы более удобны если строки разделены разделителем, в виде последовательности "новой строки", которая может быть распознана приложением на предназначенной платформе.Для Windows — это двухбайтная 'CRLF' последовательность, возврат каретки (ASCII код 13) и перевод строки (ASCII код 10).Для POSIX — LF обычно самодостаточен, в некоторых MacOS X приложениях она может быть LFCR.Существуют различные способы для автоматического заполнения столбца разделителя.В нашем примере это сделано с помощью BEFORE INSERT триггера и встроенной функции ASCII_CHAR.

Примеры использования внешних таблиц

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

CREATE TABLE ext_log
EXTERNAL FILE 'd:\externals\log_me.txt' (
   stamp   CHAR(24),
   message CHAR(100),
   crlf    CHAR(2) -- Для Windows
);

COMMIT;

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

SET TERM ^;
CREATE TRIGGER bi_ext_log FOR ext_log
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW.stamp IS NULL) THEN
    NEW.stamp = CAST (CURRENT_TIMESTAMP AS CHAR(24));
  NEW.crlf = ASCII_CHAR(13) || ASCII_CHAR(10);
END ^
COMMIT ^
SET TERM ;^

Вставка некоторых записей (это может быть сделано в обработчике исключения):

INSERT INTO ext_log (message)
VALUES('Shall I compare thee to a summer''s day?');
INSERT INTO ext_log (message)
VALUES('Thou art more lovely and more temperate');

Содержимое внешнего файла:

2015-10-07 15:19:03.4110Shall I compare thee to a summer's day?
2015-10-07 15:19:58.7600Thou art more lovely and more temperate
См. также:

ALTER TABLE, DROP TABLE, CREATE DOMAIN.

Ограничение NOT NULL

По умолчанию столбец может принимать значение NULL.

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

Значение по умолчанию

Необязательное предложение DEFAULT позволяет указать значение по умолчанию для столбца таблицы.Это значение будет помещено в столбец таблицы при выполнении оператора INSERT, если значение не будет указано для этого столбца.В качестве значения по умолчанию может быть литерал совместимый по типу, неизвестное значение NULL или контекстная переменная, тип которой совместим с типом столбца.Если значение по умолчанию явно не устанавливается, то подразумевается пустое значение, NULL.Использование выражений в значении по умолчанию недопустимо.

Столбцы основанные на домене

Для определения столбца, можно воспользоваться ранее описанным доменом.Если определение столбца основано на домене, оно может включать новое значение по умолчанию, дополнительные ограничения CHECK, предложение COLLATE, которые перекрывают значения указанные при определении домена.Определение такого столбца может включать дополнительные ограничения столбца, например NOT NULL, если домен его ещё не содержит.

Important

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

Столбцы идентификации (автоинкремент)

Столбец идентификации представляет собой столбец, связанный с внутренним генератором последовательностей.Столбцы идентификации могут быть определены либо с помощью предложения GENERATED BY DEFAULT AS IDENTITY, либо предложения GENERATED ALWAYS AS IDENTITY.

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

Чтобы использовать сгенерированное по умолчанию значение, необходимо либо указать ключевое слово DEFAULT при вставке в столбец идентификации, или просто не упоминать столбец идентификации в списке столбцов для вставки.В противном случае будет использовано указанное вами значение.

Example 1. Столбец определённый как GENERATED BY DEFAULT AS IDENTITY
CREATE TABLE greetings (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  name CHAR(50));

-- specify value "1":
INSERT INTO greetings VALUES (1, 'hi');

-- use generated default
INSERT INTO greetings VALUES (DEFAULT, 'salut');

-- use generated default
INSERT INTO greetings(ch) VALUES ('bonjour');
Note

Это поведение может быть изменено в операторе INSERT если указана директива OVERRIDING USER VALUE.Подробнее см. Директива OVERRIDING.

Если столбец идентификации задан как GENERATED ALWAYS, то его значение будет увеличиваться при каждой вставке.При попытке явно присвоить значение столбца идентификации в операторе INSERT, будет выдано сообщение об ошибке.В операторе INSERT вы можете указать ключевое слово DEFAULT вместо значения для столбца идентификации.

create table greetings (
  id INT GENERATED ALWAYS AS IDENTITY,
  name CHAR(50));

INSERT INTO greetings VALUES (DEFAULT, 'hello');

INSERT INTO greetings(ch) VALUES ('bonjour');
Note

Это поведение может быть изменено в операторе INSERT если указана директива OVERRIDING SYSTEM VALUE.Подробнее см. Директива OVERRIDING.

Необязательное предложение START WITH позволяет указать начальное значение отличное от нуля.Предложение INCREMENT [BY] устанавливает значение приращения.Значение приращения должно быть отлично от 0.По умолчанию значение приращения равно 1.

Правила

  • Тип данных столбца идентификации должен быть целым числом с нулевым масштабом. Допустимыми типами являются SMALLINT, INTEGER, BIGINT, NUMERIC(x,0) и DECIMAL(x,0);

  • Идентификационный столбец не может иметь DEFAULT и COMPUTED значений.

Note

  • Идентификационный столбец может быть изменён, чтобы стать обычным столбцом. Обычный столбец не может быть изменён, чтобы стать идентификационным.

  • Идентификационные столбцы неявно являются NOT NULL столбцами.

  • Уникальность не обеспечивается автоматически. Ограничения UNIQUE или PRIMARY KEY требуются для гарантии уникальности.
См. также:

Директива OVERRIDING.

Вычисляемые поля

Вычисляемые поля могут быть определены с помощью предложения COMPUTED [BY] или GENERATED ALWAYS AS (согласно стандарту SQL-2003). Они эквивалентны по смыслу.Для вычисляемых полей не требуется описывать тип данных (но допустимо), СУБД вычисляет подходящий тип в результате анализа выражения.В выражении требуется указать корректную операцию для типов данных столбцов, входящих в его состав.При явном указании типа столбца для вычисляемого поля результат вычисления приводится к указанному типу, то есть, например, результат числового выражения можно вывести как строку.Вычисление выражения происходит для каждой строки выбранных данных, если в операторе выборки данных SELECT, присутствует такой столбец.

Tip

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

Столбцы типа массив

Для любого типа данных кроме BLOB можно указать размерность массива, если столбец должен быть массивом.Размерность массива указывается в квадратных скобках.Чтобы не перепутать их с символами, означающими необязательные элементы, они выделены жирным шрифтом.При указании размерности массива указываются два числа через двоеточие.Первое число означает начальный номер элемента массива, второе — конечный.Если указано только одно число, то оно означает последний номер в элементе массива, а первым номером считается 1.Для многомерного массива размерности массива перечисляются через запятую.

Ограничения

Существуют четыре вида ограничений:

  • первичный ключ (PRIMARY KEY);

  • уникальный ключ (UNIQUE);

  • внешний ключ (REFERENCES или FOREIGN KEY);

  • проверочное ограничение (CHECK).

Ограничения могут быть указаны на уровне столбца (“ограничения столбцов”) или на уровне таблицы (“табличные ограничения”).Ограничения уровня таблицы необходимы, когда ключи (ограничение уникальности, первичный ключ или внешний ключ) должны быть сформированы по нескольким столбцам, или, когда ограничение CHECK включает несколько столбцов, т.е.действует на уровне записи.Синтаксис для некоторых типов ограничений может незначительно отличаться в зависимости от того определяется ограничение на уровне столбца или на уровне таблицы.

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

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

  • Вы можете смешивать ограничения столбцов и ограничения таблиц в одном операторе CREATE TABLE.

Системой автоматически создаётся индекс для первичного ключа (PRIMARY KEY), уникального ключа (UNIQUE KEY) и внешнего ключа (REFERENCES для ограничения уровня столбца, и FOREIGN KEY REFERENCES для ограничения уровня таблицы).

Имена для ограничений и их индексов

Если имя ограничения не задано, то оно автоматически будет сгенерировано системой.

Ограничения уровня столбца и их индексы автоматически именуются следующим образом:

  • Имена ограничений имеют следующий вид INTEG_<n>, где n представлено одним или несколькими числами;

  • Имена индексов имеют вид RDB$PRIMARY<n> (для индекса первичного ключа), RDB$FOREIGN<n> (для индекса внешнего ключа) или RDB$<n> (для индекса уникального ключа), где n представлено одним или несколькими числами;

Схемы автоматического формирования имён для ограничений уровня таблицы и их индексов одинаковы.

Именованные ограничения

Имя ограничения можно задать явно, если указать его в необязательном предложении CONSTRAINT.По умолчанию имя индекса ограничения будет тем же самым, что и самого ограничения.Если для индекса необходимо задать другое имя, то его можно указать в предложении USING.

Предложение USING

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

Первичный ключ (PRIMARY KEY)

Ограничение первичного ключа PRIMARY KEY строится на поле с заданным ограничением NOT NULL и требует уникальности значений столбца.Таблица может иметь только один первичный ключ.

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

  • Первичный ключ по нескольким столбцам может быть определён только на уровне таблицы.

Ограничение уникальности (UNIQUE)

Ограничение уникального ключа UNIQUE задаёт для значений столбца требование уникальности содержимого.Таблица может содержать любое количество уникальных ключей.

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

NULL в уникальных ключах

Согласно стандарту SQL-99 Firebird допускает одно или более значений NULL в столбце на который наложено ограничение UNIQUE.Это позволяет определить ограничение UNIQUE на столбцах, которые не имеют ограничения NOT NULL.

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

  • Разрешено множество записей со значением NULL во всех столбцах ключа;

  • Разрешено множество записей с различными комбинациями null и not-null значений в ключах;

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

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

Это можно резюмировать следующим примером:

RECREATE TABLE t( x int, y int, z int, unique(x,y,z));
INSERT INTO t values( NULL, 1, 1 );
INSERT INTO t values( NULL, NULL, 1 );
INSERT INTO t values( NULL, NULL, NULL );
INSERT INTO t values( NULL, NULL, NULL ); -- Разрешено
INSERT INTO t values( NULL, NULL, 1 ); -- Запрещено

Внешний ключ (FOREIGN KEY)

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

Столбцы внешнего ключа не требуют ограничения NOT NULL.

На уровне столбца ограничение внешнего ключа определяется с использованием ключевого слова REFERENCES.

... ,
ARTIFACT_ID INTEGER REFERENCES COLLECTION (ARTIFACT_ID),

В этом примере столбец ARTIFACT_ID ссылается на столбец с тем же именем в таблице COLLECTION.

На уровне таблицы могут быть определены внешний ключ над одним или несколькими столбцами.Внешние ключи над несколькими столбцами можно определить только на уровне таблицы.

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

... ,
CONSTRAINT FK_ARTSOURCE FOREIGN KEY(DEALER_ID, COUNTRY)
  REFERENCES DEALER (DEALER_ID, COUNTRY),

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

Note

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

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

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

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

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

  • NO ACTION (по умолчанию) — не будет выполнено никаких действий;

  • CASCADE — при изменении или удалении значения первичного ключа над значением внешнего ключа будут произведены те же действия. При выполнении удаления строки в главной таблице в подчинённой таблице должны быть удалены все записи, имеющие те же значения внешнего ключа, что и значение первичного (уникального) ключа удалённой строки главной таблицы. При выполнении обновления записи главной таблицы в подчинённой таблице должны быть изменены все значения внешнего ключа, имеющие те же значения, что и значение первичного (уникального) ключа изменяемой строки главной таблицы;

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

  • SET NULL — значения внешнего ключа всех соответствующих строк в подчинённой таблице устанавливаются в пустое значение NULL.

Example 1. Внешний ключ с каскадным обновлением и установкой NULL при удалении
CONSTRAINT FK_ORDERS_CUST
FOREIGN KEY (CUSTOMER) REFERENCES CUSTOMERS (ID)
ON UPDATE CASCADE ON DELETE SET NULL

Ограничение CHECK

Ограничение CHECK задаёт условие, которому должны удовлетворять значения, помещаемые в данный столбец.Условие — это логическое выражение, называемое также предикат, которое может возвращать значения TRUE (истина), FALSE (ложь) и UNKNOWN (неизвестно). Условие считается выполненным, если предикат возвращает значение TRUE или UNKNOWN (эквивалент NULL). Если предикат возвращает FALSE, то значение не будет принято.Это условие используется при добавлении в таблицу новой строки (оператор INSERT) и при изменении существующего значения столбца таблицы (оператор UPDATE), а также операторов, в которых может произойти одно из этих действий (UPDATE OR INSERT, MERGE).

Important

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

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

Example 1. CHECK ограничения уровня столбца и уровня таблицы
CREATE TABLE PLACES (
  ...
  LAT DECIMAL(9, 6) CHECK (ABS(LAT) <= 90),
  LON DECIMAL(9, 6) CHECK (ABS(LON) <= 180),
  ...
  CONSTRAINT CHK_POLES CHECK (ABS(LAT) < 90 OR LON = 0)
);

Привилегии выполнения

Необязательное предложение SQL SECURITY в спецификации таблицы позволяет задать с какими привилегиями вычисляются вычисляемые столбцы.Если выбрана опция INVOKER, то вычисляемые столбцы вычисляются с привилегиями вызывающего пользователя.Если выбрана опция DEFINER, то вычисляемые столбцы вычисляются с привилегиями определяющего пользователя (владельца). По умолчанию вычисляемые столбцы вычисляются с привилегиями вызывающего пользователя.Кроме триггеры наследуют привилегии выполнения таблицы, если они не переопределены у самих триггеров.

Tip

Привилегии выполнения по умолчанию для вновь создаваемых объектов метаданных можно изменить с помощью оператора

ALTER DATABASE SET DEFAULT SQL SECURITY {DEFINER | INVOKER}

ALTER TABLE

Назначение

Изменение структуры таблицы.

Доступно в

DSQL, ESQL.

Синтаксис
ALTER TABLE tablename
  <operation> [, <operation>];

<operation> ::=
    ADD <col_def>
  | ADD <tconstraint>
  | DROP colname
  | DROP CONSTRAINT constr_name
  | DROP SQL SECURITY
  | ALTER [COLUMN] colname <col_mod>
  | ALTER SQL SECURITY {DEFINER | INVOKER}
  | {ENABLE | DISABLE} PUBLICATION

<col_def> ::=
    <regular_col_def>
  | <computed_col_def>
  | <identity_col_def>

<regular_col_def> ::=
  colname { <datatype> | domainname }
  [DEFAULT {literal | NULL | <context_var>}]
  [NOT NULL]
  [<col_constraint>]
  [COLLATE collation_name]


<computed_col_def>  ::=
  colname [<datatype>]
  {COMPUTED [BY] | GENERATED ALWAYS AS} (<expression>)

<identity_col_def> ::=
  colname [<datatype>] {ALWAYS | GENERATED BY} DEFAULT AS IDENTITY
  [(START WITH startvalue)] [<col_constraint>]

<col_mod> ::=
     TO newname
  |  POSITION newpos
  | <regular_col_mod>
  | <computed_col_mod>
  | <identity_col_mod>

<regular_col_mod> ::=
  |  TYPE { <datatype>  | domain_name }
  |  SET DEFAULT {literal | NULL | <context_var>}
  |  DROP DEFAULT
  |  SET NOT NULL
  |  DROP NOT NULL

<computed_col_mod> ::=
  [TYPE <datatype>] {GENERATED ALWAYS AS | COMPUTED [BY]} (<expression>)

<identity_col_mod> ::=
     <alter identity column option> ...
  |  SET GENERATED { ALWAYS | BY DEFAULT } [<alter identity column option> ...]
  |  DROP INDENITY

<alter identity column option> ::=
     RESTART [ WITH startvalue ]
  |  SET INCREMENT [BY] incrementvalue

<datatype> ::=
    <scalar_datatype> | <blob_datatype> | <array_datatype>

<scalar_datatype> ::=  См. Синтаксис скалярных типов данных

<blob_datatype> ::= См. Синтаксис типа данных BLOB

<array_datatype> ::= См. Синтаксис массивов

<col_constraint> ::=
  [CONSTRAINT constr_name]
  {   UNIQUE [<using_index>]
    | PRIMARY KEY [<using_index>]
    | REFERENCES other_table [(other_col)]
        [ON DELETE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [ON UPDATE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [<using_index>]
    | CHECK (<check_condition>)
  }

<tconstraint> ::=
  [CONSTRAINT constr_name]
  {   UNIQUE (<col_list>) [<using_index>]
    | PRIMARY KEY (<col_list>) [<using_index>]
    | FOREIGN KEY (<col_list>)
      REFERENCES other_table [(<col_list>)]
        [ON DELETE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [ON UPDATE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [<using_index>]
    | CHECK (<check_condition>)
  }

<col_list> ::= colname [, colname ...]

<using_index> ::= USING [ASC[ENDING] | DESC[ENDING]] INDEX indexname

<check_condition> ::=
    <val> <operator> <val>
  | <val> [NOT] BETWEEN <val> AND <val>
  | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)
  | <val> IS [NOT] NULL
  | <val> IS [NOT] DISTINCT <val>
  | <val> IS [NOT] {TRUE | FALSE | UNKNOWN}
  | <val> [NOT] CONTAINING <val>
  | <val> [NOT] STARTING [WITH] <val>
  | <val> [NOT] LIKE <val> [ESCAPE <val>]
  | <val> [NOT] SIMILAR TO <val> [ESCAPE <val>]
  | <val> <operator> {ALL | SOME | ANY} (<select_list>)
  | [NOT] EXISTS (<select_expr>)
  | [NOT] SINGULAR (<select_expr>)
  | (<check_condition>)
  | NOT <check_condition>
  | <check_condition> OR <check_condition>
  | <check_condition> AND <check_condition>


<operator> ::=
    <> | != | ^= | ~= | = | < | > | <= | >=
  | !< | ^< | ~< | !> | ^> | ~>

<val> ::=
    colname [[<array_idx> [, <array_idx> ...]]]
  | literal
  | <context_var>
  | <expression>
  | NULL
  | NEXT VALUE FOR genname
  | GEN_ID(genname, <val>)
  | CAST(<val> AS <datatype>)
  | (<select_one>)
  | func(<val> [, <val> ...])


<cast_type> ::=
   <datatype>
  | [TYPE OF] domain_name
  | TYPE OF COLUMN rel.colname
Table 1. Параметры оператора ALTER TABLE
Параметр Описание

tablename

Имя таблицы.

operation

Одна из допустимых операций по изменению структуры таблицы.

colname

Имя столбца таблицы, может содержать до 63 символов.Должно быть уникальным внутри таблицы.

newname

Новое имя столбца таблицы, может содержать до 63 символов.Должно быть уникальным внутри таблицы.

gencolname

Имя вычисляемого столбца таблицы.

idencolname

Имя столбца идентификации.

newpos

Новая позиция столбца в таблице.Целое число в диапазоне от 1 до количества столбцов таблицы.

datatype

Тип данных SQL.

domain_name

Имя домена.

startvalue

Начальное значение столбца идентификации.

incrementvalue

Значение приращения для столбца идентификации.Должно быть отлично от 0.

col_constraint

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

tconstraint

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

constr_name

Имя ограничения, может содержать до 63 символов.

other_table

Имя таблицы, на которую ссылается внешний ключ.

other_col

Столбец таблицы, на которую ссылается внешний ключ.

using_index

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

literal

Литерал.

context_var

Любая контекстная переменная, тип которой совместим с типом данных столбца.

check_condition

Условие проверки ограничения.Выполняется, если оценивается как TRUE или NULL/UNKNOWN.

collation_name

Имя порядка сортировки.Необходимо указывать если вы хотите чтобы порядок сортировки для столбца отличался от порядка сортировки для набора символов по умолчанию этого столбца.

select_one

Оператор SELECT выбирающий один столбец и возвращающий только одну строку.

select_list

Оператор SELECT выбирающий один столбец и возвращающий ноль и более строк.

select_expr

Оператор SELECT выбирающий несколько столбцов и возвращающий ноль и более строк.

experssion

Выражение.

genname

Имя последовательности (генератора).

func

Скалярная функция.

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

Счётчик форматов

Некоторые изменения структуры таблицы увеличивают счётчик форматов, закреплённый за каждой таблицей.Количество форматов для каждой таблицы ограничено значением 255.После того как счётчик форматов достигнет этого значения, вы не сможете больше менять структуру таблицы.

Сброс счётчика форматов
Для сброса счётчика форматов необходимо сделать резервное копирование и восстановление базы данных (утилитой gbak).

Предложение ADD

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

Воздействие на счётчик форматов:

  • При каждом добавлении нового столбца номер формата увеличивается на единицу.

  • Добавление нового ограничения таблицы не влечёт за собой увеличение номера формата.

Example 1. Добавление столбца в таблицу
ALTER TABLE COUNTRY
ADD CAPITAL VARCHAR(25);
Example 2. Добавление столбца с ограничением NOT NULL
ALTER TABLE OBJECTS
ADD QUANTITY INT DEFAULT 1 NOT NULL;
Important

Обратите внимание на предложение DEFAULT, которое обязательно при добавлении ограничения NOT NULL, если в таблице есть данные.Дело в том, что в этом случае также происходит проверка данных на допустимость.А поскольку при добавлении нового столбца, он для всех строк таблицы содержит значение NULL, будет сгенерировано исключение.
Example 3. Добавление столбца с ограничением уникальности и удаление другого столбца
ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25) UNIQUE,
  DROP CURRENCY;

Для добавления ограничений уровня таблицы необходимо использовать предложение ADD [CONSTRAINT].

Example 4. Добавление проверочного ограничения и внешнего ключа
ALTER TABLE JOB
ADD CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY),
ADD FOREIGN KEY (JOB_COUNTRY)
REFERENCES COUNTRY (COUNTRY);
Warning

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

Предложение DROP

Предложение DROP удаляет указанный столбец таблицы.Столбец таблицы не может быть удалён, если от него существуют зависимости.Другими словами для успешного удаления столбца на него должны отсутствовать ссылки.Ссылки на столбец могут содержаться:

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

  • в индексах;

  • в хранимых процедурах и триггерах;

  • в представлениях.

При каждом удалении столбца номер формата увеличивается на единицу.

Предложение DROP CONSTRAINT

Предложение DROP CONSTRAINT удаляет указанное ограничение столбца или таблицы.Ограничение первичного ключа или уникального ключа не могут быть удалены, если они используются в ограничении внешнего ключа другой таблицы.В этом случае необходимо удалить ограничение FOREIGN KEY до удаления PRIMARY KEY или UNIQUE ключа, на которые оно ссылается.

Удаление ограничения столбца или ограничения таблицы не влечёт за собой увеличение номера формата.

Предложение DROP SQL SECURITY

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

Предложение ALTER [COLUMN]

Предложение ALTER [COLUMN] позволяет изменить следующие характеристики существующих столбцов:

  • изменение имени (не изменяет номер формата);

  • изменение типа данных (увеличивает номер формата на единицу);

  • изменение позиции столбца в списке столбцов таблицы (не изменяет номер формата);

  • удаление значения по умолчанию столбца (не изменяет номер формата);

  • добавление значения по умолчанию столбца (не изменяет номер формата);

  • изменение типа и выражения для вычисляемого столбца (не изменяет номер формата);

  • добавление ограничения NOT NULL (не изменяет номера формата);

  • удаление ограничения NOT NULL (не изменяет номера формата).

Переименование столбца

Ключевое слово TO переименовывает существующий столбец.Новое имя столбца не должно присутствовать в таблице.

Невозможно изменение имени столбца, если этот столбец включён в какое-либо ограничение — первичный или уникальный ключ, внешний ключ, ограничение столбца или проверочное ограничение таблицы CHECK.Имя столбца также нельзя изменить, если этот столбец таблицы используется в каком-либо триггере, в хранимой процедуре или представлении.

Example 1. Переименование столбца таблицы
ALTER TABLE STOCK
ALTER COLUMN MODELNAME TO NAME;

Изменение типа столбца

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

Если столбец был объявлен как массив, то изменить ни его тип, ни размерность нельзя.

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

Example 1. Изменение типа столбца таблицы
ALTER TABLE STOCK
ALTER COLUMN ITEMID TYPE BIGINT;

Изменение позиции столбца

Ключевое слово POSITION изменяет позицию существующего столбца.Позиции столбцов нумеруются с единицы.

  • Если будет задан номер позиции меньше 1, то будет выдано соответствующее сообщение об ошибке.

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

Example 1. Изменение позиции столбца таблицы
ALTER TABLE STOCK
ALTER COLUMN ITEMID POSITION 5;

Установка и удаление значения по умолчанию

Предложение DROP DEFAULT удаляет значение по умолчанию для столбца таблицы.

  • Если столбец основан на домене со значением по умолчанию — доменное значение перекроет это удаление.

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

Example 1. Удаление значения по умолчанию для столбца
ALTER TABLE STOCK
ALTER COLUMN MODEL DROP DEFAULT;

Предложение SET DEFAULT устанавливает значение по умолчанию для столбца таблицы.Если столбец уже имел значение по умолчанию, то оно будет заменено новым.Значение по умолчанию для столбца всегда перекрывает доменное значение по умолчанию.

Example 2. Установка значения по умолчанию для столбца
ALTER TABLE STOCK
ALTER COLUMN MODEL SET DEFAULT 1;

Установка и удаление ограничения NOT NULL

Предложение SET NOT NULL добавляет ограничение NOT NULL для столбца таблицы.

Note

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

Явное ограничение NOT NULL на столбце, базирующегося на домене, преобладает над установками домена.В этом случае изменение домена для допустимости значения NULL, не распространяется на столбец таблицы.
Example 1. Добавление ограничения NOT NULL
ALTER TABLE STOCK
ALTER COLUMN PROPID SET NOT NULL;

Предложение DROP NOT NULL удаляет ограничение NOT NULL для столбца таблицы.Если столбец основан на домене с ограничением NOT NULL, то ограничение домена перекроет это удаление.

Example 2. Удаление ограничения NOT NULL
ALTER TABLE STOCK
ALTER COLUMN ITEMID DROP NOT NULL;

Изменение столбцов идентификации

Для столбцов идентификации позволено изменять способ генерации, начальное значение и значение приращения.

Предложение SET GENERATED позволяет изменить способ генерации столбца идентификации.Существует два способа генерации столбца идентификации:

  • BY DEFAULT столбцы позволяют переписать сгенерированное системой значение в операторах INSERT, UPDATE OR INSERT, MERGE просто указав значение этого столбца в списке значений.

  • ALWAYS столбцы не позволяют переписать сгенерированное системой значение, при попытке переписать значение такого столбца идентификации будет выдана ошибка. Переписать значение этого столбца в операторе INSERT можно только при указании директивы OVERRIDING SYSTEM VALUE.

Example 1. Изменение способа генерации столбца идентификации
ALTER TABLE objects
ALTER ID SET GENERATED ALWAYS;

Если указано только предложение RESTART, то происходит сброс значения генератора в ноль.Необязательное предложение WITH позволяет указать для нового значения внутреннего генератора отличное от нуля значение.

Example 2. Изменение текущего значения генератора для столбца идентификации
ALTER TABLE objects
ALTER ID RESTART WITH 100;

Предложение SET INCREMENT [BY] позволяет изменить значение приращения столбца идентификации.Значение приращения должно быть отлично от 0.

Example 3. Изменение приращения столбца идентификации
ALTER TABLE objects
ALTER ID SET INCREMENT BY 2;

В одном операторе можно изменить сразу несколько свойств столбца идентификации, например:

Example 4. Изменение нескольких свойств столбца идентификации
ALTER TABLE objects
ALTER ID SET GENERATED ALWAYS RESTART SET INCREMENT BY 2;

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

Example 5. Превращение столбца идентификации в обычный столбец
ALTER TABLE objects
ALTER ID DROP INDENTITY;

Изменение вычисляемых столбцов

Для вычисляемых столбцов (GENERATED ALWAYS AS или COMPUTED BY) позволяется изменить тип и выражение вычисляемого столбца.Невозможно изменить обычный столбец на вычисляемый и наоборот.

Example 1. Изменение вычисляемых столбцов
ALTER TABLE SALARY_HISTORY
ALTER NEW_SALARY GENERATED ALWAYS
AS (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
ALTER SALARY_CHANGE COMPUTED
BY (OLD_SALARY * PERCENT_CHANGE / 100);

Не изменяемые атрибуты

На данный момент не существует возможности изменить сортировку по умолчанию.

Предложение ALTER SQL SECURITY

Предложение ALTER SQL SECURITY позволяет изменить привилегии с которыми вычисляются вычисляемые столбцы.Если выбрана опция INVOKER, то вычисляемые столбцы вычисляются с привилегиями вызывающего пользователя.Если выбрана опция DEFINER, то вычисляемые столбцы вычисляются с привилегиями определяющего пользователя (владельца). По умолчанию вычисляемые столбцы вычисляются с привилегиями вызывающего пользователя.Кроме того триггеры наследуют привилегии выполнения у таблицы, если они не переопределены у самих триггеров.

ALTER TABLE COUNTRY
ALTER SQL SECURITY DEFINER;

Управление репликацией

Предложение ENABLE PUBLICATION включает таблицу в набор репликации (публикацию). Соответственно предложение DISABLE PUBLICATION исключает таблицу из набора репликации.

Example 1. Добавление таблицы в набор репликации
ALTER TABLE COUNTRY
ENABLE PUBLICATION;

Кто может изменить таблицу?

Выполнить оператор ALTER TABLE могут:

См. также:

CREATE TABLE, RECREATE TABLE.

DROP TABLE

Назначение

Удаление существующей таблицы.

Доступно в

DSQL, ESQL.

Синтаксис
DROP TABLE tablename
Table 1. Параметры оператора DROP TABLE
Параметр Описание

tablename

Имя таблицы.

Оператор DROP TABLE удаляет существующую таблицу.Если таблица имеет зависимости, то удаление не будет произведено.При удалении таблицы будут также удалены все триггеры на её события и индексы, построенные для её полей.

Example 1. Удаление таблицы
DROP TABLE COUNTRY;

Кто может удалить таблицу?

Выполнить оператор DROP TABLE могут:

См. также:

CREATE TABLE, RECREATE TABLE.

RECREATE TABLE

Назначение

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

Доступно в

DSQL.

Синтаксис
RECREATE [GLOBAL TEMPORARY] TABLE tablename
  [EXTERNAL [FILE] 'filespec']
  (<col_def> [, <col_def> | <tconstraint> ...])
  [ON COMMIT {DELETE | PRESERVE} ROWS]
  [SQL SECURITY {DEFINER | INVOKER}]

Полное описание определений столбцов и ограничений таблицы смотрите в разделе CREATE TABLE.

Создаёт или пересоздаёт таблицу.Если таблица с таким именем уже существует, то оператор RECREATE TABLE попытается удалить её и создать новую.Оператор RECREATE TABLE не выполнится, если существующая таблица имеет зависимости.

Примеры

Example 1. Создание или пересоздание таблицы
RECREATE TABLE COUNTRY (
  COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
  CURRENCY VARCHAR(10) NOT NULL);
См. также:

CREATE TABLE, DROP TABLE.