Creating a Domain - Firebird

The data definition language (DDL) syntax for creating a domain is

Domain Identifier

When you create a domain in the database, you must specify an identifier for the domain that is globally unique in the database. Developers often use a special prefix or suffix in domain identifiers, to facilitate self-documentation. For example:

Data Type for the Domain

The data type is the only required attribute that must be set for the domain—all other attributes are optional. It specifies the SQL data type that will apply to a column defined using the domain. Any native Firebird data type can be used. It is not possible to use a domain as the type for another domain.

The following statement creates a domain that defines an array of CHARACTER type:

The next statement creates a BLOB domain with a text subtype that has an assigned character set: overriding the default character set of the database. It effectively creates a specialized memo type for storing Japanese text:

The DEFAULT Attribute

A domain can define a default value that the server will use when inserting a new row if the INSERT statement does not include the column in its specification list. Defaults can save time and error during data entry. For example, a possible default for a DATE column could be today’s date, or to write the CURRENT_USER context variable into a UserName column.

Default values can be

  • A constant. The default value is a user-specified string, numeric value, or date value—often used for placing a “zero value” into a non-nullable column.
  • CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, or a Firebird predefined date literal.
  • USER, CURRENT_USER, or CURRENT_ROLE (if roles are applicable).
  • CURRENT_CONNECTION or CURRENT_TRANSACTION.

The following statement creates a domain that must have a positive value greater than 1,000. If the INSERT statement does not present a VALUE, the column will be assigned the default value of 9,999:

If your operating system supports the use of multi-byte characters in user names, or you have used a multi-byte character set when defining roles, then any column into which these defaults will be stored must be defined using a compatible character set.

When Defaults Won’t Work

It is a common mistake to assume that a default value will be used whenever Firebird receives NULL in a defaulted column. When relying on defaults, it must be understood that a default will be applied

  • Only upon insertion of a new row

    AND

  • Only if the INSERT statement does not include the defaulted column in its column list

If your application includes the defaulted column in the INSERT statement and sends NULL in the values list, then NULL will be stored —or cause an exception in a non-nullable column —regardless of any default defined.

The NOT NULL Attribute

Include this attribute in the domain if you want to force all columns created with this domain to contain a value.

NULL—which is not a value, but a state —will always be disallowed on any column bearing the NOT NULL attribute.

You cannot override the NOT NULL attribute on a domain. Consider the benefit of not including it in the domain’s attributes, thereby leaving it as an option to add the attribute when columns are defined.

CHECK Data Conditions

The CHECK constraint provides a wide scope for providing domain attributes that restrict the content of data that can be stored in columns using the domain. The CHECK constraint sets a search condition (dom_search_condition) that must be true before data can be accepted into these columns.

Here’s the syntax for CHECK constraints:

The VALUE Keyword

VALUE is a placeholder for any constant or variable value or expression result that would be submitted through SQL for storing in a column defined using the domain. The CHECK constraint causes VALUE to be validated against the restrictions defined in the conditions. If validation fails, an exception is raised.

If NULL is to be permitted in lieu of a value, the rule must be accommodated in the CHECK constraint, for example:

The next statement creates a domain that disallows any input value of 1000 or less, but it also implicitly disallows NULL by presuming a value:

The next statement restricts VALUE to being one of four specific values:

A validation condition can be made to search for a specific pattern in a string input. For example, the next validation check enforces a rule that requires a bracketed area code to precede telecom numbers (e.g., (09)438894749):

Multiple CHECK Conditions

A domain can have only one CHECK clause but multiple conditions can be ANDed or ORed within this single clause. Care is needed with bracketing the condition expressions to avoid getting logical exceptions when the DDL statement is prepared.

For example, this statement fails:

It excepts with a “token unknown” error upon the word “and.” The corrected statement encloses the entire list of conditions within outer brackets and succeeds:

A domain’s CHECK constraint cannot be overridden by one declared during column definition. However, a column can extend its use of the domain’s CHECK constraint by adding its own CHECK conditions.

Dependencies in CHECK Constraints

In tables, CHECK constraints can be defined legally with expressions referring to other columns in the same table or, less desirably, referring to columns in other database objects (tables, stored procedures).

Domains, of course, cannot refer to other domains. It is possible, although almost always unwise, to define a domain that refers to a column in an existing table. For example:

Conceptually, it’s not such a wild concept to use select expressions in domains. Firebird allows it but it really does not follow through and implement it as an integrated design. It is an accidental by-product, not a feature.

As a database design approach, it integrates poorly with the referential integrity features that are purposefully implemented. Foreign key relationships enforce existence rules in all areas, whereas the scope of a CHECK constraint is limited to data entry.

CHECK constraints with inter-table dependencies would be disabled on restoring the database from a backup. They would silently fail to compile because the dependent tables had not yet been re-created. To put them back into effect, they would have to be reinstated manually, by some means. Implementing such checks at domain level has mind-boggling implications.

In some situations, where the dependency applies to a highly static table whose name is low in alphabetical sequence (gbak restores tables in alphabetical order), such a CHECK condition might be faintly arguable. The problem remains that the domain has no control over what happens to data stored in tables beyond the event of checking the data coming into columns that use it.

If you absolutely need to use this kind of check condition, apply it as an extra condition when you declare the column. Preferably, evaluate all of the alternatives—including the hand-coding of referential triggers in cases where foreign keys on lookup fields would cause recognized problems with index selectivity.

The CHARSET/CHARACTER SET Attribute

For systems that need to be concerned about multiple character sets inside a single database, declaring character set–specific domains for all of your text columns (CHAR, VARCHAR, BLOB SUB_TYPE 1, and arrays of character types) can be a very elegant way to deal with it. Refer to the previous chapter for character set definition syntax.

The COLLATE Attribute

A COLLATE clause in a domain creation statement specifies an explicit collation sequence for CHAR or VARCHAR domains. You must choose a collation that is supported for the domain’s declared, inherited, or implied character set.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics