In Firebird, columns are nullable by default. The optional NOT NULL clause specifies that the column cannot take NULL in place of a value.

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 character set specified during the creation of the database will be used by default. If no character set was specified during the creation of the database, the NONE character set is applied by default. 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 collation sequence that is default for the specified character set during the creation of the column is applied by default.

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.

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.

Four types of constraints can be specified. They are:

• Primary key (PRIMARY KEY)

• Unique key (UNIQUE)

• Foreign key (REFERENCES)

• CHECK constraint (CHECK)

Constraints can be specified at column level (column constraints) or at table level (table constraints). Table-level constraints are needed when keys (uniqueness constraint, Primary Key, Foreign Key) are to be formed across multiple columns and when a CHECK constraint involves other columns in the row besides the column being defined. Syntax for some types of constraint may differ slightly according to whether the constraint is being defined at 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

• Table-level constraints are specified after all of the column definitions. They 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)
  |);

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]

  |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

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

6. Creating a connection-scoped global temporary table.

     |CREATE GLOBAL TEMPORARY TABLE MYCONNGTT (
     |  ID  INTEGER NOT NULL PRIMARY KEY,
     |  TXT VARCHAR(32),
     |  TS  TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
     |ON COMMIT PRESERVE ROWS;
   

7. Creating a transaction-scoped global temporary table that uses a foreign key to reference a connection-scoped global temporary table. The ON COMMIT sub-clause is optional because DELETE ROWS is the default.

     |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
     |) ON COMMIT DELETE ROWS;
   

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 <column name> 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)

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 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.

The following alterations are not supported:

• Enabling or disabling the NOT NULL constraint on a column

• Changing the default collation for a character type column

Only the table owner and administrators have the authority to use ALTER TABLE.

1. Adding the CAPITAL column to the COUNTRY table.

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

2. Adding the CAPITAL column with the 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. 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”