Entering Metadata Statements

In this exercise, you use the CREATE DOMAIN statement to create domains that you will use later to specify column datatypes.

Create Some Domains

In the following exercise you will define four domains. The first three specify only a datatype. The fourth one is more complex. In each case, the domain will be useful for several different columns, not just the column for which it is named.

Note: You should be connected to the TUTORIAL database when you begin this exercise.

1. Click on the SQL icon () and type the following code in the SQL Statement Area to define a domain called FIRSTNAME that has a datatype of VARCHAR(15).

   CREATE DOMAIN FIRSTNAME AS VARCHAR(15)

2. Execute the statement: click , press F5, or choose Query > Execute.

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

3. 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 datatype, it includes check constraints to ensure that the department number is either “000”, alphabetically between “0” and “999”, or NULL. Pay attention to 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)

Tip: When you’re typing an SQL statement that has parentheses, take a moment to count the left parentheses and the right parentheses and make sure that there are the same number of each. 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 the line breaks when parsing the statement. Enter the whole statement before executing it.

You don’t need to commit your work, because CREATE statements are DDL (data definition language) statements. You turned Autocommit DDL on in the Session Settings earlier in this tutorial, so all these DDL statements have been committed automatically.

Note: You can manually commit your SQL statements, making them permanent by selecting Transactions > Commit. This commits any uncommitted DML and DDL statements.

Advance To:

Data Definition Files