 |
|
|
 | Language and Coding for Character Strings |  | Data Types |
A domain is a data type with properties (default value or integrity constraint) marked with an identifier.
If a domain is defined in a schema, you can use the identifier wherever a type is used (e.g. for a column description in a table or for declaring variables).
A domain is usually created for a special data type that is used many times in a schema. The major advantages are:
Let us consider an accounting application that controls invoices. We create a domain INVOICE_NUMBER of Integer type and use it in the invoices table, invoice items table, and when declaring variables in procedures that manipulate invoices.
When the invoice numbers should be a 15-digit number, you only need to change the domain type to BIGINT. If the need arises that the invoice number should have a slash in it, the domain type changes to a character string type.
If two tables are related using referential integrity for the invoice number, then the change of the invoice number type would be difficult without the use of a domain. It would require the deletion of the referential integrity, change the column type in both tables, and then create the referential integrity again from scratch. When using the INVOICE_NUMBER domain it is sufficient to change only the definition.
If it is required to prevent an empty invoice number, it would be sufficient to set NOT NULL in the domain definition. If the invoice number should have some special format, you can control the format with the CHECK clause. These conditions will be checked each time the INVOICE_NUMBER domain is used.
Domains are defined, altered and deleted using the CREATE DOMAIN, ALTER DOMAIN and DROP DOMAIN commands.
In the 602SQL Client domains are created with an interactive designer.
A domain identifier can be used anywhere a type can be used. If there are specifications in the domain definition that cannot be used in the current context (like NOT NULL or CHECK when declaring variables), they are ignored.
The default value specified in the domain definition may be applied only in the context where the default value makes sense (e.g. when declaring a column in a table, declaring a variable or declaring a parameter in a stored procedure). The default value is ignored for a function return value type or in the CAST operation.
If the default value is specified in the domain definition and where the domain is used, then the specification from the domain definition is used.
The NOT NULL clause has the same effect in the domain definition and in the domain usage.
The CHECK clause (integrity constraint) has the same effect in the domain definition and in the domain usage. If it is specified in both places, both specifications will be used.
If a domain is used in tables that are filled with data, the data is preserved when the domain is altered. If the original domain type can be converted using the CAST function to the new domain type, then the data in the column whose type is set by this domain is preserved as well. In other cases there will be default values or NULL values in the column.
If an error occurs when restructuring tables (e.g. some value cannot be converted to the new type), then the restructuring and domain change is rolled back.
If the tables that use the domain are filled with a large amount of data, the domain change may take a long time. It may also cause the transaction and database file to enlarge considerably.
All operations after a domain alteration are executed as a single transaction. The transaction is complete after the alteration is complete and cannot be rolled back using the ROLLBACK statement.
| Language and Coding for Character Strings | | Data Types |