FirebirdSQL logo
 DOMAININDEX 
Действия внешнего ключа

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

  • Предложение 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)
);

docnext count = 23

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

Необязательное предложение 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.