You can use the CHARACTER SET clause to specify the character set for the CHAR, VARCHAR and BLOB (text subtype) types. If the character set is not specified, the default character set of the database - at time of the creation of the column - will be used. If the database has no default character set, the NONE character set is applied. In this case, data is stored and retrieved the way it was submitted. Data in any encoding can be added to such a column, but it is not possible to add this data to a column with a different encoding. No transliteration is performed between the source and destination encodings, which may result in errors.

The optional COLLATE clause allows you to specify the collation sequence for character data types, including BLOB SUB_TYPE TEXT. If no collation sequence is specified, the default collation sequence for the specified character set - at time of the creation of the column - is applied.

The optional DEFAULT clause allows you to specify the default value for the table column. This value will be added to the column when an INSERT statement is executed if no value was specified for it and that column was omitted from the INSERT command.

The default value can be a literal of a compatible type, a context variable that is type-compatible with the data type of the column, or NULL, if the column allows it. If no default value is explicitly specified, NULL is implied.

An expression cannot be used as a default value.

To define a column, you can use a previously defined domain. If the definition of a column is based on a domain, it may contain a new default value, additional CHECK constraints, and a COLLATE clause that will override the values specified in the domain definition. The definition of such a column may contain additional column constraints (for instance, NOT NULL), if the domain does not have it.

Identity columns can be defined using the GENERATED BY DEFAULT AS IDENTITY clause. The identity column is the column associated with internal sequence generator. Its value is set automatically every time it is not specified in the INSERT statement. The optional START WITH clause allows you to specify an initial value other than 1.

Rules

• The data type of an identity column must be an exact number type with zero scale. Allowed types are thus SMALLINT, INTEGER, BIGINT, NUMERIC(p[,0]) and DECIMAL(p[,0]).

• An identity column cannot have a DEFAULT or COMPUTED value.

Calculated fields can be defined with the COMPUTED [BY] or GENERATED ALWAYS AS clause (according to the SQL:2003 standard). They mean the same. Describing the data type is not required (but possible) for calculated fields, as the DBMS calculates and stores the appropriate type as a result of the expression analysis. Appropriate operations for the data types included in an expression must be specified precisely.

If the data type is explicitly specified for a calculated field, the calculation result is converted to the specified type. This means, for instance, that the result of a numeric expression could be rendered as a string.

In a query that selects a COMPUTED BY column, the expression is evaluated for each row of the selected data.

• If the column is to be an array, the base type can be any SQL data type except BLOB and array.

• The dimensions of the array are specified between square brackets. (In the Syntax block these brackets appear in quotes to distinguish them from the square brackets that identify optional syntax elements.)

• For each array dimension, one or two integer numbers define the lower and upper boundaries of its index range:

  • By default, arrays are 1-based. The lower boundary is implicit and only the upper boundary need be specified. A single number smaller than 1 defines the range num..1 and a number greater than 1 defines the range 1..num.

  • Two numbers separated by a colon (:) and optional whitespace, the second greater than the first, can be used to define the range explicitly. One or both boundaries can be less than zero, as long as the upper boundary is greater than the lower.

• When the array has multiple dimensions, the range definitions for each dimension must be separated by commas and optional whitespace.

• Subscripts are validated only if an array actually exists. It means that no error messages regarding invalid subscripts will be returned if selecting a specific element returns nothing or if an array field is NULL.

Five types of constraints can be specified. They are:

• Primary key (PRIMARY KEY)

• Unique key (UNIQUE)

• Foreign key (REFERENCES)

• CHECK constraint (CHECK)

• NOT NULL constraint (NOT NULL)

Constraints can be specified at column level (column constraints) or at table level (table constraints). Table-level constraints are required when keys (unique constraint, Primary Key, Foreign Key) consist of multiple columns and when a CHECK constraint involves other columns in the row besides the column being defined. The NOT NULL constraint can only be specified as a column constraint. Syntax for some types of constraint may differ slightly according to whether the constraint is defined at the column or table level.

• A column-level constraint is specified during a column definition, after all column attributes except COLLATION are specified, and can involve only the column specified in that definition

• A table-level constraints can only be specified after the definitions of the columns used in the constraint.

• Table-level constraints are a more flexible way to set constraints, since they can cater for constraints involving multiple columns

• You can mix column-level and table-level constraints in the same CREATE TABLE statement

The system automatically creates the corresponding index for a primary key (PRIMARY KEY), a unique key (UNIQUE) and a foreign key (REFERENCES for a column-level constraint, FOREIGN KEY REFERENCES for one at the table level).

  |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 ); -- Permitted
  |INSERT INTO t values( NULL, NULL, 1 );    -- Not permitted

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

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

  |...
  |  CONSTRAINT FK_ORDERS_CUST
  |    FOREIGN KEY (CUSTOMER) REFERENCES CUSTOMERS (ID)
  |      ON UPDATE CASCADE ON DELETE SET NULL

  |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)
  |);

The CREATE TABLE statement can be executed by:

• Administrators

• Users with the CREATE TABLE privilege

The user executing the CREATE TABLE statement becomes the owner of the table.

1. Creating the COUNTRY table with the primary key specified as a column constraint.

     |CREATE TABLE COUNTRY (
     |  COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
     |  CURRENCY VARCHAR(10) NOT NULL
     |);
   

2. Creating the STOCK table with the named primary key specified at the column level and the named unique key specified at the table level.

     |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)
     |);
   

3. Creating the JOB table with a primary key constraint spanning two columns, a foreign key constraint for the COUNTRY table and a table-level CHECK constraint. The table also contains an array of 5 elements.

      |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),
      |  FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY)
      |  ON UPDATE CASCADE
      |  ON DELETE SET NULL,
      |  CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY)
      |);
   

4. Creating the PROJECT table with primary, foreign and unique key constraints with custom index names specified with the USING clause.

      |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
      |);
   

5. Creating a table with an identity column

      |create table objects (
      |  id integer generated by default as identity primary key,
      |  name varchar(15)
      |);
      | 
      |insert into objects (name) values ('Table');
      |insert into objects (id, name) values (10, 'Computer');
      |insert into objects (name) values ('Book');
      | 
      |select * from objects order by id;
      | 
      |          ID NAME
      |============ ===============
      |           1 Table
      |           2 Book
      |          10 Computer
   

6. Creating the SALARY_HISTORY table with two computed fields. The first one is declared according to the SQL:2003 standard, while the second one is declared according to the traditional declaration of computed fields in 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)
      |);
   

Global temporary tables have persistent metadata, but their contents are transaction-bound (the default) or connection-bound. Every transaction or connection has its own private instance of a GTT, isolated from all the others. Instances are only created if and when the GTT is referenced. They are destroyed when the transaction ends or on disconnection. The metadata of a GTT can be modified or removed using ALTER TABLE and DROP TABLE, respectively.

Syntax

  |CREATE GLOBAL TEMPORARY TABLE tablename
  |  (<column_def> [, {<column_def> | <table_constraint>} ...])
  |  [ON COMMIT {DELETE | PRESERVE} ROWS]

Since Firebird 3.0, GTTs are writable in read-only transactions. The effect is as follows:

Read-only transaction in read-write database

Writable in both ON COMMIT PRESERVE ROWS and ON COMMIT DELETE ROWS

Read-only transaction in read-only database

Writable in ON COMMIT DELETE ROWS only

  |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

The optional EXTERNAL [FILE] clause specifies that the table is stored outside the database in an external text file of fixed-length records. The columns of a table stored in an external file can be of any type except BLOB or ARRAY, although for most purposes, only columns of CHAR types would be useful.

All you can do with a table stored in an external file is insert new rows (INSERT) and query the data (SELECT). Updating existing data (UPDATE) and deleting rows (DELETE) are not possible.

A file that is defined as an external table must be located on a storage device that is physically present on the machine where the Firebird server runs and, if the parameter ExternalFileAccess in the firebird.conf configuration file is Restrict, it must be in one of the directories listed there as the argument for Restrict. If the file does not exist yet, Firebird will create it on first access.

  |CREATE TABLE ext_log
  |  EXTERNAL FILE 'd:\externals\log_me.txt' (
  |  stamp CHAR (24),
  |  message CHAR(100),
  |  crlf CHAR(2) -- for a Windows context
  |);
  |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

Some changes in the structure of a table increment the metadata change counter (version count) assigned to every table. The number of metadata changes is limited to 255 for each table. Once the counter reaches the 255 limit, you will not be able to make any further changes to the structure of the table without resetting the counter.

With the ADD clause you can add a new column or a new table constraint. The syntax for defining the column and the syntax of defining the table constraint correspond with those described for CREATE TABLE statement.

The DROP colname clause deletes the specified column from the table. An attempt to drop a column will fail if anything references it. Consider the following items as sources of potential dependencies:

• column or table constraints

• indexes

• stored procedures and triggers

• views

The DROP CONSTRAINT clause deletes the specified column-level or table-level constraint.

A PRIMARY KEY or UNIQUE key constraint cannot be deleted if it is referenced by a FOREIGN KEY constraint in another table. It will be necessary to drop that FOREIGN KEY constraint before attempting to drop the PRIMARY KEY or UNIQUE key constraint it references.

With the ALTER [COLUMN] clause, attributes of existing columns can be modified without the need to drop and re-add the column. Permitted modifications are:

• change the name (does not affect the metadata change counter)

• change the data type (increases the metadata change counter by one)

• change the column position in the column list of the table (does not affect the metadata change counter)

• delete the default column value (does not affect the metadata change counter)

• set a default column value or change the existing default (does not affect the metadata change counter)

• change the type and expression for a computed column (does not affect the metadata change counter)

• set the NOT NULL constraint (does not affect the metadata change counter)

• drop the NOT NULL constraint (does not affect the metadata change counter)

The TO keyword with a new identifier renames an existing column. The table must not have an existing column that has the same identifier.

It will not be possible to change the name of a column that is included in any constraint: PRIMARY KEY, UNIQUE key, FOREIGN KEY, column constraint or the CHECK constraint of the table.

Renaming a column will also be disallowed if the column is used in any trigger, stored procedure or view.

The keyword TYPE changes the data type of an existing column to another, allowable type. A type change that might result in data loss will be disallowed. As an example, the number of characters in the new type for a CHAR or VARCHAR column cannot be smaller than the existing specification for it.

If the column was declared as an array, no change to its type or its number of dimensions is permitted.

The data type of a column that is involved in a foreign key, primary key or unique constraint cannot be changed at all.

The POSITION keyword changes the position of an existing column in the notional left-to-right layout of the record.

Numbering of column positions starts at 1.

• If a position less than 1 is specified, an error message will be returned

• If a position number is greater than the number of columns in the table, its new position will be adjusted silently to match the number of columns.

The optional DROP DEFAULT clause deletes the default value for the column if it was put there previously by a CREATE TABLE or ALTER TABLE statement.

• If the column is based on a domain with a default value, the default value will revert to the domain default

• An execution error will be raised if an attempt is made to delete the default value of a column which has no default value or whose default value is domain-based

The optional SET DEFAULT clause sets a default value for the column. If the column already has a default value, it will be replaced with the new one. The default value applied to a column always overrides one inherited from a domain.

The SET NOT NULL clause adds a NOT NULL constraint on an existing table column. Contrary to definition in CREATE TABLE, it is not possible to specify a constraint name.

Dropping the NOT NULL constraint from the column if its type is a domain that also has a NOT NULL constraint, has no observable effect until the NOT NULL constraint is dropped from the domain as well.

The data type and expression underlying a computed column can be modified using a COMPUTED [BY] or GENERATED ALWAYS AS clause in the ALTER TABLE ALTER [COLUMN] statement. Converting a regular column to a computed one and vice versa are not permitted.

For identity columns (GENERATED BY DEFAULT AS IDENTITY), it is possible to restart the sequence used for generating identity values. If only the RESTART clause is specified, then the sequence resets to the initial value specified during CREATE TABLE. If the optional WITH start_value clause is specified, the sequence will restart with the specified value.

It is not possible to convert an existing column to an identity column, or to convert an identity column to a normal column. Firebird 4 will introduce the ability to convert an identity column to a normal column.

The following alterations are not supported:

• Changing the collation of a character type column

The ALTER TABLE statement can be executed by:

• Administrators

• The owner of the table

• Users with the ALTER ANY TABLE privilege

1. Adding the CAPITAL column to the COUNTRY table.

     |ALTER TABLE COUNTRY
     |  ADD CAPITAL VARCHAR(25);
   

2. Adding the CAPITAL column with the NOT NULL and UNIQUE constraint and deleting the CURRENCY column.

     |ALTER TABLE COUNTRY
     |  ADD CAPITAL VARCHAR(25) NOT NULL UNIQUE,
     |  DROP CURRENCY;
   

3. Adding the CHK_SALARY check constraint and a foreign key to the JOB table.

     |ALTER TABLE JOB
     |  ADD CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY),
     |  ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);
   

4. Setting default value for the MODEL field, changing the type of the ITEMID column and renaming the MODELNAME column.

     |ALTER TABLE STOCK
     |  ALTER COLUMN MODEL SET DEFAULT 1,
     |  ALTER COLUMN ITEMID TYPE BIGINT,
     |  ALTER COLUMN MODELNAME TO NAME;
   

5. Restarting the sequence of an identity column.

     |ALTER TABLE objects
     |  ALTER ID RESTART WITH 100;
   

6. Changing the computed columns NEW_SALARY and SALARY_CHANGE.

     |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);
   

See alsoSection 5.4.1, “CREATE TABLE”, Section 5.4.3, “DROP TABLE”, Section 5.3.1, “CREATE DOMAIN”

The DROP TABLE statement can be executed by:

• Administrators

• The owner of the table

• Users with the DROP ANY TABLE privilege

Dropping the COUNTRY table.

  |DROP TABLE COUNTRY;

See alsoSection 5.4.1, “CREATE TABLE”, Section 5.4.2, “ALTER TABLE”, Section 5.4.4, “RECREATE TABLE”

Creating or recreating the COUNTRY table.

  |RECREATE TABLE COUNTRY (
  |  COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
  |  CURRENCY VARCHAR(10) NOT NULL
  |);

See alsoSection 5.4.1, “CREATE TABLE”, Section 5.4.3, “DROP TABLE”