Firebird Documentation IndexFirebird 2.5 Language ReferenceData Types and Subtypes → Custom Data Types—Domains
Firebird Home Firebird Home Prev: Conversion of Data TypesFirebird Documentation IndexUp: Data Types and SubtypesNext: Common Language Elements

Custom Data Types—Domains

Table of Contents

Domain Attributes
Domain Override
Creating and Administering Domains
Altering a Domain
Deleting (Dropping) a Domain

In Firebird, the concept of a “user-defined data type” is implemented in the form of the domain. Creating a domain does not truly create a new data type, of course. A domain provides the means to encapsulate an existing data type with a set of attributes and make this “capsule” available for multiple usage across the whole database. If several tables need columns defined with identical or nearly identical attributes, a domain makes sense.

Domain usage is not limited to column definitions for tables and views. Domains can be used to declare input and output parameters and variables in PSQL code.

Domain Attributes

A domain definition contains required and optional attributes. The data type is a required attribute. Optional attributes include:

  • a default value
  • to allow or forbid NULL
  • CHECK constraints
  • character set (for character data types and text BLOB fields)
  • collation (for character data types)

Sample domain definition: 

       CREATE DOMAIN BOOL3 AS SMALLINT
         CHECK (VALUE IS NULL OR VALUE IN (0, 1));
	

See also: Explicit Data Type Conversion for the description of differences in the data conversion mechanism when domains are specified for the TYPE OF and TYPE OF COLUMN modifiers.

Domain Override

While defining a column using a domain, it is possible to override some of the attributes inherited from the domain. Table 3.9 summarises the rules for domain override.

Table 3.9. Rules for Overriding Domain Attributes in Column Definition

Attribute Override? Comments
Data type No
Default value Yes
Text character set Yes It can be also used to restore the default database values for the column
Text collation sequence Yes
CHECK constraints Yes To add new conditions to the check, you can use the corresponding CHECK clauses in the CREATE and ALTER statements at the table level.
NOT NULL No Often it is better to leave domain nullable in its definition and decide whether to make it NOT NULL when using the domain to define columns.


Creating and Administering Domains

A domain is created with the DDL statement CREATE DOMAIN.

Short Syntax: 

       CREATE DOMAIN <name> [AS] <type>
       [DEFAULT {<const> | <literal> | NULL | <context_var>}]
       [NOT NULL] [CHECK (<condition>)]
       [COLLATE collation];
        

See also: CREATE DOMAIN in the Data Definition Language (DDL) section.

Altering a Domain

To change the attributes of a domain, use the DDL statement ALTER DOMAIN. With this statement you can

  • rename the domain
  • change the data type
  • delete the current default value
  • set a new default value
  • delete an existing CHECK constraint
  • add a new CHECK constraint

Short Syntax: 

       ALTER DOMAIN name
       [{TO new_name}]
       [{SET DEFAULT {literal | NULL | <context_var>} |
        DROP DEFAULT}]
       [{ADD [CONSTRAINT] CHECK (<dom_condition>) |
         DROP CONSTRAINT}]
       [{TYPE <datatype>}];
          

When planning to alter a domain, its dependencies must be taken into account: whether there are table columns, any variables, input and/or output parameters with the type of this domain declared in the PSQL code. If you change domains in haste, without carefully checking them, your code may stop working!

Important

When you convert data types in a domain, you must not perform any conversions that may result in data loss. Also, for example, if you convert VARCHAR to INTEGER, check carefully that all data using this domain can be successfully converted.

See also: ALTER DOMAIN in the Data Definition Language (DDL) section.

Deleting (Dropping) a Domain

The DDL statement DROP DOMAIN deletes a domain from the database, provided it is not in use by any other database objects.

Syntax: 

       DROP DOMAIN name
          

Important

Any user connected to the database can delete a domain.

Example: 

       DROP DOMAIN Test_Domain
          

See also: DROP DOMAIN in the Data Definition Language (DDL) section.

Prev: Conversion of Data TypesFirebird Documentation IndexUp: Data Types and SubtypesNext: Common Language Elements
Firebird Documentation IndexFirebird 2.5 Language ReferenceData Types and Subtypes → Custom Data Types—Domains