InterBase Quick Start: Part II - Creating Domains
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.
- Character set and optional collation order (for
See CREATE DOMAIN for details about the definition of a domain.
- Login to the Local Server in IBConsole as the user TUTOR.
- Note: You create the user TUTOR in Part 1 - Create a New User.
- Connect to the TUTORIAL database.
- Select the TUTORIAL database and use Database > Connect.
- Note: You create the database TUTORIAL in Part 1 - Create a Database.
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.
- Open the Interactive SQL window.
- Type the following code in the SQL statement area to define a domain called
firstnamethat has a data type of
CREATE DOMAIN firstname AS VARCHAR(15)
- Execute the statement.
- Now create two more domains,
empno. Execute each statement separately before entering the next one.
CREATE DOMAIN lastname AS VARCHAR(20)
CREATE DOMAIN empno AS SMALLINT
- 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
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)
- 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
CHECKclause 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").
CREATE DOMAINstatement 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
CREATEstatements are DDL statements (you turn auto commit feature on in Session Settings earlier in this tutorial).