Indexes that enforce named constraints may now be named with user-defined identifiers.

Previously, although it was possible to created named PRIMARY, FOREIGN KEY and UNIQUE constraints, the identifier of the automatically-generated enforcing index was calculated by the system, e.g., RDB$FOREIGN13, and could not be altered. This remains the default behaviour when named constraints are not used.

However, language extensions have been added to enable

Syntax Pattern

  ...
  [ADD] CONSTRAINT [<constraint-identifier>]
  <constraint-type> <constraint-definition>
  [USING [ASC[ENDING] | DESC[ENDING]] INDEX <index_name>]
      

Examples

i) Named constraint and explicitly-named index:

  CREATE TABLE ATEST (
    ID BIGINT NOT NULL,
    DATA VARCHAR(10));
  COMMIT;
      

The following statement will create a primary key constraint named PK_ATEST and an enforcing, descending index named IDX_PK_ATEST:

  ALTER TABLE ATEST
  ADD CONSTRAINT PK_ATEST PRIMARY KEY(ID)
  USING DESC INDEX IDX_PK_ATEST;
  COMMIT;
      

ii) Alternative to i) above:

  CREATE TABLE ATEST (
    ID BIGINT NOT NULL,
    DATA VARCHAR(10),
    CONSTRAINT PK_ATEST PRIMARY KEY(ID)
    USING DESC INDEX IDX_PK_ATEST;
      

iii) This statement creates the table ATEST with the primary key PK_ATEST. The enforcing index is also named PK_ATEST.:

  CREATE TABLE ATEST (
    ID BIGINT NOT NULL,
    DATA VARCHAR(10),
    CONSTRAINT PK_ATEST PRIMARY KEY(ID));
      

Triggers are enhanced to enable them to handle multiple row-level operations conditionally.

Syntax Pattern

  CREATE TRIGGER name FOR table
    [ACTIVE | INACTIVE]
    {BEFORE | AFTER} <multiple_action>
    [POSITION number]
  AS trigger_body

  <multiple_action> ::= <single_action> [OR <single_action> [OR <single_action>]]
  <single_action> ::= {INSERT | UPDATE | DELETE}
      

Examples

i)

  CREATE TRIGGER TRIGGER1 FOR TABLE1
  ACTIVE BEFORE INSERT OR UPDATE AS
  ...;
      

ii)

  CREATE TRIGGER TRIGGER2 FOR TABLE2
  ACTIVE AFTER INSERT OR UPDATE OR DELETE AS
  ...;
      

ODS Change

Encoding of field RDB$TRIGGER_TYPE (relation RDB$TRIGGERS) has been extended to allow complex trigger actions. For details, refer to the document readme.universal_triggers.txt in the /doc/sql.extensions branch of the Firebird CVS tree.

Notes

Exactly the same as CREATE VIEW if the view does not already exist. If it does exist, RECREATE VIEW will try to drop it and create a completely new object. RECREATE VIEW will fail if the object is in use.

Uses the same syntax as CREATE VIEW.

Statement that will either create a new trigger or procedure (if it does not already exist) or alter it (if it already exists) and recompile it. The CREATE OR ALTER syntax preserves existing dependencies and permissions.

Syntax is as for CREATE TRIGGER | CREATE PROCEDURE, respectively, except for the additional keywords "OR ALTER".

When the count of metadata changes on any single table reaches the maximum of 255, the database becomes unavailable. Backup and restore are required in order to reset the change count and make the database once again available. The intention of this feature is to enforce a database cleanup when table structures have undergone a lot of changes, not to inhibit useful capabilities in the engine.

Previously, each time a trigger was set ACTIVE|INACTIVE by an ALTER TRIGGER statement, the change count for the associated table would be incremented. This affected the usefulness of disabling and re-enabling trigger code for regular operations, since it would cause the change count to rise quickly. Now, it is not treated as a metadata change for table versioning purposes.

It is now possible to apply a UNIQUE constraint or a unique index to a column that does not have the NOT NULL constraint, in compliance with SQL-99. Be cautious about using this if you plan to revert your database to Firebird 1.0.x or any InterBase version, since those older servers will see the index as corrupt.

Syntax Details

  <unique constraint or index definition> ::=
    <unique specification> ( <unique column list UCL> )
  <unique specification> ::=
    {{[constraint-name]UNIQUE | UNIQUE INDEX index-name]} |
      [constraint-name] PRIMARY KEY}
      

where <unique column list> can contain one or more columns without the NOT NULL attribute, if <unique specification> is UNIQUE or UNIQUE INDEX index-name.

The constraint allows existence of only those rows for which search condition (i) or (ii) evaluates as True, according to the following logic:

The constraint allows existence of only those rows for which the aforementioned <search condition> evaluates to True. In a unique index or under a UNIQUE constraint, two sets of column values will be considered distinct and thus allowed if:

Examples

UNIQUE constraint:

  CREATE TABLE t (
    a INTEGER,
    b INTEGER,
    CONSTRAINT pk UNIQUE (a, b));
      

or UNIQUE index:

  CREATE TABLE t (a INTEGER, b INTEGER);
  COMMIT;

  CREATE UNIQUE INDEX uqx ON t(a, b);
  COMMIT;

  INSERT INTO t
    VALUES (NULL, NULL); /* ok, nulls allowed */

  INSERT INTO t
    VALUES (1, 2); /* as are non-nulls */

  INSERT INTO t
    VALUES (1, NULL); /* and combinations */

  INSERT INTO t
    VALUES (NULL, NULL); /* ok, all pairs of nulls are distinct */
      

but not:

  INSERT INTO t
    VALUES (1, NULL);
    /* fails because all corresponding non-null segments match */
      

It means that the PRIMARY KEY constraint doesn't allow NULLs whilst the UNIQUE constraint and unique indexes allow an arbitrary number of NULLs. For multi-column result sets of ( SELECT UCL FROM TN ), the common rules for NULLs are applied, i.e. (1, NULL) is distinct from (NULL, 1) and one (NULL, NULL) is distinct from any other (NULL, NULL).

Enables unused generators to be removed from the database. Storage will be freed for re-use upon the next RESTORE. Available in SQL and DSQL.

Syntax Pattern

  DROP GENERATOR <generator name>;
      

The following were implemented in Firebird 1.0. They are described again here for the convenience of the reader.