InterBase Quick Start: Part II - Creating Domains

Go Up to InterBase Quick Start: Part II - Data Definition

A domain is a customized column definition that you use to create tables. When you create a table, you specify the characteristics of each column in the table. Usually there are several columns in different tables in a database that have the same characteristics. Rather than entering the same complex definition for each column, you can create a name for the collection of characteristics. This named set of column characteristics is called a domain. You can use the domain name in a column definition rather than typing out the full definition.

Note:
If you use domains in your column definitions, you must create the domains beforehand.

Some of the charcateristics that you may specify for a domain are:

• Data type.
• Default value.
• Nullability.
• CHECK constraints.
• Character set and optional collation order (for CHAR and VARCHAR columns).

See CREATE DOMAIN for details about the definition of a domain.

Connecting to the TUTORIAL database

1. Login to the Local Server in IBConsole as the user TUTOR.

   Note:
   You create the user TUTOR in Part 1 - Create a New User.

2. Connect to the TUTORIAL database.
   • Select the TUTORIAL database and use Go to the Home tab and select Connect.

   Note:
   You create the database TUTORIAL in Part 1 - Create a Database.

Creating Domains

Use the CREATE DOMAIN statement to create domains that you use later to specify column data types.

Follow the steps below to define four domains. The first three specify only a data type. The fourth is more complex. In each case, the domain will be useful for several different columns, not just the column for which it is named.

1. Open the Interactive SQL window.
2. Type the following code in the SQL statement area to define a domain called firstname that has a data type of VARCHAR(15):

   CREATE DOMAIN firstname AS VARCHAR(15)
   

3. Execute the statement.
4. Now create two more domains, lastname and empno. Execute each statement separately before entering the next one.

   CREATE DOMAIN lastname AS VARCHAR(20)
   

   CREATE DOMAIN empno AS SMALLINT
   

5. Next, enter and execute the following code to define a domain for department numbers. The domain is defined as a three-character string. In addition to the data type, it includes check constraints to ensure that the department number is either "000", alphabetically between "0" and "999", or NULL. Pay attention to the parentheses and quotes as you enter this:

   CREATE DOMAIN deptno AS CHAR(3) CHECK ( VALUE = '000'
   OR
   (
     VALUE > '0'
     AND
     VALUE <= '999'
   )
   OR
   VALUE IS NULL)
   

Special Considerations

• When you type an SQL statement that has parentheses, take a moment to count left parentheses and right parentheses and make sure that they match. Mismatched parentheses are a major source of errors in SQL code. In the example above, there are nested parentheses: the CHECK clause is enclosed in parentheses because it contains three parts ("A OR B OR C") and the second part of the clause has parentheses because it also contains multiple parts ("A AND B").

• The CREATE DOMAIN statement above is divided into several lines to make it easy for users to follow the syntax. InterBase ignores line breaks when parsing the statement. Enter the whole statement before executing it.

• You do not need to commit your work because CREATE statements are DDL statements (you turn auto commit feature on in Session Settings earlier in this tutorial).

Advance To

• Data Definition Files