FirebirdSQL logo
 DOMAININDEX 

Примеры

Example 1. Создание таблицы
CREATE TABLE COUNTRY (
  COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
  CURRENCY VARCHAR(10) NOT NULL);
Example 2. Создание таблицы с заданием именованного первичного и уникального ключей
CREATE TABLE STOCK (
  MODEL SMALLINT NOT NULL CONSTRAINT PK_STOCK PRIMARY KEY,
  MODELNAME CHAR(10) NOT NULL,
  ITEMID INTEGER NOT NULL,
  CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID));
Example 3. Создание таблицы с добавлением её в набор репликации
CREATE TABLE STOCK (
  MODEL SMALLINT NOT NULL CONSTRAINT PK_STOCK PRIMARY KEY,
  MODELNAME CHAR(10) NOT NULL,
  ITEMID INTEGER NOT NULL,
  CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID))
ENABLE PUBLICATION;
Example 4. Таблица с полем массивом
CREATE TABLE JOB (
    JOB_CODE         JOBCODE NOT NULL,
    JOB_GRADE        JOBGRADE NOT NULL,
    JOB_COUNTRY      COUNTRYNAME,
    JOB_TITLE        VARCHAR(25) NOT NULL,
    MIN_SALARY       NUMERIC(18, 2) DEFAULT 0 NOT NULL,
    MAX_SALARY       NUMERIC(18, 2) NOT NULL,
    JOB_REQUIREMENT  BLOB SUB_TYPE 1,
    LANGUAGE_REQ     VARCHAR(15) [1:5],
    PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY),
    FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY)
      ON UPDATE CASCADE
      ON DELETE SET NULL,
    CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY)
);
Example 5. Создание таблицы с ограничением первичного, внешнего и уникального ключа для которых заданы пользовательские имена индексов
CREATE TABLE PROJECT (
    PROJ_ID    PROJNO NOT NULL,
    PROJ_NAME  VARCHAR(20) NOT NULL UNIQUE
      USING DESC INDEX IDX_PROJNAME,
    PROJ_DESC    BLOB SUB_TYPE 1,
    TEAM_LEADER  EMPNO,
    PRODUCT      PRODTYPE,
    CONSTRAINT PK_PROJECT PRIMARY KEY (PROJ_ID)
      USING INDEX IDX_PROJ_ID,
    FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE (EMP_NO)
      USING INDEX IDX_LEADER
);
Example 6. Создание таблицы со столбцом идентификации BY DEFAULT
CREATE TABLE objects (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(15)
);

INSERT INTO objects (name) VALUES ('Table');
INSERT INTO objects (name) VALUES ('Book');
INSERT INTO objects (id, name) VALUES (10, 'Computer');

SELECT * FROM objects;
ID           NAME
============ ===============
           1 Table
           2 Book
          10 Computer
Example 7. Создание таблицы со столбцом идентификации ALWAYS
CREATE TABLE objects (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(15)
);

INSERT INTO objects (name) VALUES ('Table');
INSERT INTO objects (name) VALUES ('Book');
INSERT INTO objects (id, name) VALUES (DEFAULT, 'Computer');

SELECT * FROM objects;
ID           NAME
============ ===============
           1 Table
           2 Book
           3 Computer
Example 8. Создание таблицы со столбцом идентификации с начальным значением равным 10 и приращением равным 2
CREATE TABLE objects (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY (STER WITH 10 INCREMENT BY 2) PRIMARY KEY,
  name VARCHAR(15)
);

INSERT INTO objects (name) VALUES ('Table');
INSERT INTO objects (name) VALUES ('Book');
ID           NAME
============ ===============
          12 Table
          14 Book
Example 9. Создание таблицы с вычисляемыми полями
CREATE TABLE SALARY_HISTORY (
    EMP_NO          EMPNO NOT NULL,
    CHANGE_DATE     TIMESTAMP DEFAULT 'NOW' NOT NULL,
    UPDATER_ID      VARCHAR(20) NOT NULL,
    OLD_SALARY      SALARY NOT NULL,
    PERCENT_CHANGE  DOUBLE PRECISION DEFAULT 0 NOT NULL,
    SALARY_CHANGE   GENERATED ALWAYS AS
      (OLD_SALARY * PERCENT_CHANGE / 100),
    NEW_SALARY      COMPUTED BY
      (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100)
);

Поле SALARY_CHANGE объявлено согласно стандарту SQL::2003, поле NEW_SALARY в классическом стиле объявления вычисляемых полей в Firebird.

CREATE TABLE SALARY_HISTORY
(
    EMP_NO          EMPNO NOT NULL,
    CHANGE_DATE     TIMESTAMP DEFAULT 'NOW' NOT NULL,
    UPDATER_ID      VARCHAR(20) NOT NULL,
    OLD_SALARY      SALARY NOT NULL,
    PERCENT_CHANGE  DOUBLE PRECISION DEFAULT 0 NOT NULL,
    SALARY_CHANGE   GENERATED ALWAYS AS
      (OLD_SALARY * PERCENT_CHANGE / 100),
    NEW_SALARY      COMPUTED BY
      (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100)
)
SQL SECURITY DEFINER;

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

Глобальные временные таблицы (GTT)

Глобальные временные таблицы (в дальнейшем сокращённо “GTT”) так же, как и обычные таблицы, являются постоянными метаданными, но данные в них ограничены по времени существования транзакцией (значение по умолчанию) или соединением с БД.Каждая транзакция или соединение имеет свой собственный экземпляр GTT с данными, изолированный от всех остальных.Экземпляры создаются только при условии обращения к GTT, и данные в ней удаляются при завершении транзакции или отключении от БД.Метаданные GTT могут быть изменены или удалены с помощью инструкций ALTER TABLE и DROP TABLE.

Синтаксис
CREATE GLOBAL TEMPORARY TABLE name
  (<column_def> [, {<column_def> | <table_constraint>} ...])
  [ON COMMIT {DELETE | PRESERVE} ROWS]
  [SQL SECURITY {DEFINER | INVOKER}]

Если в операторе создания глобальной временной таблицы указано необязательное предложение ON COMMIT DELETE ROWS, то будет создана GTT транзакционного уровня (по умолчанию). При указании предложения ON COMMIT PRESERVE ROWS — будет создана GTT уровня соединения с базой данных.

Предложение EXTERNAL [FILE] нельзя использовать для глобальной временной таблицы.

Note

Операторы COMMIT RETAINING и ROLLBACK RETAINING сохраняют данные в глобальных временных таблицах объявленных как ON COMMIT DELETE ROWS.В Firebird 2.x была ошибка: COMMIT RETAINING и ROLLBACK RETAINING делали записи не видимыми для текущей транзакции.Для возврата поведения 2.x установить параметр ClearGTTAtRetaining равным 1 в firebird.conf.Этот параметр может быть удалён в Firebird 5.0.

Ограничения GTT

GTT обладают всеми атрибутами обычных таблиц (ключи, внешние ключи, индексы и триггеры), но имеют ряд ограничений:

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

  • GTT уровня соединения (“PRESERVE ROWS”) GTT не могут ссылаться на GTT транзакционного уровня (“DELETE ROWS”);

  • Ограничения домена не могут ссылаться на любую GTT;

  • Уничтожения экземпляра GTT в конце своего жизненного цикла не вызывает срабатывания триггеров до/после удаления.

Tip

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

SELECT t.rdb$type_name
FROM rdb$relations r
JOIN rdb$types t ON r.rdb$relation_type = t.rdb$type
WHERE t.rdb$field_name = 'RDB$RELATION_TYPE'
  AND r.rdb$relation_name = 'TABLENAME'

Для просмотра информации о типах всех таблиц используйте запрос:

SELECT r.rdb$relation_name, t.rdb$type_name
FROM rdb$relations r
JOIN rdb$types t ON r.rdb$relation_type = t.rdb$type
WHERE t.rdb$field_name = 'RDB$RELATION_TYPE'
  AND coalesce (r.rdb$system_flag, 0) = 0

Поле RDB$TYPE_NAME будет отображать PERSISTENT для обычной таблицы, VIEW для представления, GLOBAL_TEMPORARY_PRESERVE для GTT уровня соединения, и GLOBAL_TEMPORARY_DELETE для GTT уровня транзакции.

Примеры

Example 1. Создание глобальной временной таблицы уровня соединения
CREATE GLOBAL TEMPORARY TABLE MYCONNGTT (
    ID INTEGER NOT NULL PRIMARY KEY,
    TXT VARCHAR(32),
    TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
ON COMMIT PRESERVE ROWS;
Example 2. Создание глобальной временной таблицы уровня транзакции ссылающейся внешним ключом на глобальную временную таблицу уровня соединения.
CREATE GLOBAL TEMPORARY TABLE MYTXGTT (
    ID INTEGER NOT NULL PRIMARY KEY,
    PARENT_ID INTEGER NOT NULL REFERENCES MYCONNGTT(ID),
    TXT VARCHAR(32),
    TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

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

Необязательное предложение 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 позволяет доступ к внешним файлам в любом месте файловой системы хоста. Это создаёт уязвимость и не рекомендуется к использованию.