FirebirdSQL logo
 DOMAININDEX 

Computed Columns

Computed columns can be defined with the COMPUTED [BY] or GENERATED ALWAYS AS clause (the SQL standard alternative to COMPUTED [BY]).Specifying the data type is optional;if not specified, the appropriate type will be derived from the expression.

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 converted to a string.

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

Tip

Instead of a computed column, in some cases it makes sense to use a regular column whose value is calculated in triggers for adding and updating data.It may reduce the performance of inserting/updating records, but it will increase the performance of data selection.

Defining an Array Column

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

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

Constraints

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 table-level).

Names for Constraints and Their Indexes

Constraints and their indexes are named automatically if no name was specified using the CONSTRAINT clause:

  • The constraint name has the form INTEG_n, where n represents one or more digits

  • The index name has the form RDB$PRIMARYn (for a primary key index), RDB$FOREIGNn (for a foreign key index) or RDB$n (for a unique key index).