The purpose of column-specific properties is to guard the semantic integrity of the data in the model; that is, to ensure that it reflects reality in a sensible way. The integrity of the data model is at risk if you can substitute a name for a telephone number or if you can enter a fraction where only integers are allowed.

To define column-specific properties, first define the constraints that a data value must satisfy before it can be part of the column. Use the following constraints to specify column-specific properties:

• Data types (user-defined types and built-in types)
• Default values
• Check constraints

Extended Data Types

• Opaque data types. You can use these encapsulated data types to define columns in the same way that you use built-in types. When you create an opaque data type, you also define the functions, operators, and aggregates to operate on the type. For information about opaque types, see the CREATE OPAQUE TYPE statement in the Informix Guide to SQL: Syntax and the user guide Extending INFORMIX-Universal Server: Data Types.
• Distinct data types. These data types have the same representation as, but are distinct from, existing data types. You can create a distinct type from a built-in type, opaque type, named row type, or other distinct type. For information about distinct types, see Chapter 3, "Environment Variables" in the Informix Guide to SQL: Reference and the CREATE DISTINCT TYPE statement in the Informix Guide to SQL: Syntax.
• Complex data types. These data types combine one or more existing data types to create a new data type. A complex data type allows access to each of its component data types. For information about complex types, see Chapter 10, "Understanding Complex Data Types."

Choosing a Data Type

Every column in a table must have a data type that is chosen from the built-in or extended types that the database server supports. The choice of data type is important for the following reasons:

• It establishes the basic properties of the column; that is, the set of valid data items that the column can store.
• It determines the kinds of operations that you can perform on the data. For example, you cannot apply aggregate functions, such as SUM, to columns with a character data type.
• It determines how much space each data item occupies on disk. The space required to accommodate data items is not as important for small tables as it for tables with tens or hundreds of thousands of rows. When a table reaches that many rows, the difference between a 4-byte and an 8-byte type can be crucial.

• SERIAL and INTEGER
• SERIAL8 and INT8

(1 of 2)

Figure 9-1 Choosing a Data Type

(2 of 2)

Numeric Data Types

Counters and Codes: INTEGER, SMALLINT, and INT8

Both types are stored as signed binary integers. INTEGER values have 32 bits and can represent whole numbers from -(231 -1) through 231-1; that is, from
-2,147,483,647 through 2,147,483,647. (The maximum negative number,
-2,147,483,248, is reserved and cannot be used.)

SMALLINT values have only 16 bits. They can represent whole numbers from -32,767 through 32,767. (The maximum negative number, -32,768, is reserved and cannot be used.)

The INTEGER and SMALLINT data types have the following advantages:

• They take up little space (2 bytes per value for SMALLINT and 4 bytes per value for INTEGER).
• Arithmetic expressions such as SUM and MAX as well as sort comparisons can be done very efficiently on them.

The INT8 data type is stored as a signed binary integer, which uses 8 bytes per value. Although INT8 takes up twice the space as the INTEGER data type, INT8 has the advantage of a significantly larger range of data representation. INT8 can represent integers ranging from - (2 63 -1) through 2 63 -1; that is from - 9,223,372,036,854,775,807 through 9,223,372,036,854,775,807. (The maximum negative number, - 9,223,372,036,854,775,808, is reserved and cannot be used.)

Automatic Sequences: SERIAL and SERIAL8

The SERIAL data type can yield up to 231-1 positive integers. Consequently, the database server uses all the positive serial numbers by the time it inserts 231-1 rows in a table. For most users the exhaustion of the positive serial numbers is not a concern, however, because a single application would need to insert a row every second for 68 years, or 68 applications would need to insert a row every second for a year, to use all the positive serial numbers. However, if all the positive serial numbers were used, the database server would continue to generate new numbers. It would treat the next serial quantity as a signed integer. Because the database server uses only positive values, it would simply wrap around and start to generate integer values that begin with a 1.

The SERIAL8 data type can yield up to 263 -1 positive integers. With a reasonable starting value, it is virtually impossible to cause a SERIAL8 value to wrap around during insertions.

For SERIAL and SERIAL8 data types, the sequence of generated numbers always increases. When rows are deleted from the table, their serial numbers are not reused. Rows that are sorted on a SERIAL or SERIAL8 column are returned in the order in which they were created. That cannot be said of any other data types.

You can specify the initial value in a SERIAL or SERIAL8 column in the CREATE TABLE statement. This makes it possible to generate different subsequences of system-assigned keys in different tables. The stores7 database uses this technique. In stores7, the customer numbers begin at 101, and the order numbers start at 1001. As long as this small business does not register more than 899 customers, all customer numbers have three digits, and order numbers have four.

A SERIAL or SERIAL8 column is not automatically a unique column. If you want to be perfectly sure that no duplicate serial numbers occur, you must apply a unique constraint (see "Using CREATE TABLE"). If you define the table using the interactive schema editor in DB-Access or SQL Editor, it automatically applies a unique constraint to any SERIAL or SERIAL8 column.

The SERIAL and SERIAL8 data types have the following advantages:

• They provide a convenient way to generate system-assigned keys.
• They produce unique numeric codes even when multiple users are updating the table.
• Different tables can use different ranges of numbers.

• Only one SERIAL or SERIAL8 column is permitted in a table.
• They can produce only arbitrary numbers.

You cannot set the next value below the current maximum value in the column because doing so causes the database server to generate duplicate numbers in certain situations. However, you can set the next value to any value higher than the current maximum, thus creating gaps in the sequence.

Approximate Numbers: FLOAT and SMALLFLOAT

The floating-point data types are designed for these applications. They can represent any numerical quantity, fractional or whole, over a wide range of magnitudes from the cosmic to the microscopic. For example, they can easily represent both the average distance from the Earth to the Sun (1.5 ¥ 109 meters) or Planck's constant (6.625 ¥ 10-27). Their only restriction is their limited precision. Floating-point numbers retain only the most significant digits of their value. If a value has no more digits than a floating-point number can store, the value is stored exactly. If it has more digits, it is stored in approximate form, with its least-significant digits treated as zeros.

This lack of exactitude is fine for many uses, but you should never use a floating-point data type to record money or any other quantity whose least significant digits should not be changed to zero.

Two sizes of floating-point data types exist. The FLOAT type is a double-precision, binary floating-point number as implemented in the C language on your computer. A FLOAT data type value usually takes up 8 bytes. The SMALLFLOAT (also known as REAL) data type is a single-precision, binary floating-point number that usually takes up 4 bytes. The main difference between the two data types is their precision. A FLOAT column retains about 16 digits of its values; a SMALLFLOAT column retains only about 8 digits.

Floating-point numbers have the following advantages:

• They store very large and very small numbers, including fractional ones.
• They represent numbers compactly in 4 or 8 bytes.
• Arithmetic functions such as AVG, MIN, and sort comparisons are efficient on these data types.

Adjustable-Precision Floating Point: DECIMAL(p)

The magnitude of a DECIMAL(p) number ranges from 10-130 to 10124.

It is easy to be confused about decimal data types. The one under discussion is DECIMAL(p); that is, DECIMAL with only a precision specified. The size of DECIMAL(p) numbers depends on their precision; they occupy 1+p/2 bytes (rounded up to a whole number, if necessary).

DECIMAL(p) has the following advantages over FLOAT:

• Precision can be set to suit the application, from highly approximate to highly precise.
• Numbers with as many as 32 digits can be represented exactly.
• Storage is used in proportion to the precision of the number.
• Every Informix database server supports the same precision and range of magnitudes, regardless of the host operating system.

• Performing arithmetic and sorts on DECIMAL(p) values is somewhat slower than on FLOAT values.
• Many programming languages do not support the DECIMAL(p) data format the way that they support FLOAT and INTEGER. When a program extracts a DECIMAL(p) value from the database, it might have to convert the value to another format for processing.

These numbers are fixed-point numbers because the decimal point is fixed at a specific place, regardless of the value of the number. The DECIMAL(p,s) data type is designed to hold them. When you specify a column of this type, you write its precision (p) as the total number of digits that it can store, from 1 to 32. You write its scale (s) as the number of those digits that fall to the right of the decimal point. (Figure 9-2 shows the relation between precision and scale.) Scale can be zero, meaning it stores only whole numbers. When only whole numbers are stored, DECIMAL(p,s) provides a way of storing integers of up to 32 digits.

Figure 9-2 The Relation Between Precision and Scale in a Fixed-Point Number

Like the DECIMAL(p) data type, DECIMAL(p,s) takes up space in proportion to its precision. One value occupies 1+p/2 bytes, rounded up to a whole number of bytes.

The MONEY type is identical to DECIMAL(p,s), but with one extra feature. Whenever the database server converts a MONEY value to characters for display, it automatically includes a currency symbol.

The advantages of DECIMAL(p,s) over INTEGER and FLOAT are that much greater precision is available (up to 32 digits as compared with 10 digits for INTEGER and 16 digits for FLOAT), and both the precision and the amount of storage required can be adjusted to suit the application.

The disadvantages are that arithmetic operations are less efficient and that many programming languages do not support numbers in this form. Therefore, when a program extracts a number, it usually must convert the number to another numeric form for processing. (However, INFORMIX-4GL programs can use DECIMAL(p,s) and MONEY values directly.)

GLS

Choosing a currency format

$7,822.45

For non-English locales, you can change the current format by means of the MONETARY category of the locale file. For more information on using locales, refer to Chapter 1 of the Guide to GLS Functionality.

To customize this currency format, choose your locale appropriately or set the DBMONEY environment variable. For more information, see Chapter 3, "Environment Variables" of the Informix Guide to SQL: Reference.

Chronological Data Types

Calendar Dates: DATE

The DATE format has ample precision to carry dates into the far future (58,000 centuries). Negative DATE values are interpreted as counts of days prior to the epoch date; that is, a DATE value of -1 represents the day December 30, 1899.

Because DATE values are integers, Informix database servers permit them to be used in arithmetic expressions. For example, you can take the average of a DATE column, or you can add 7 or 365 to a DATE column. In addition, a rich set of functions exists specifically for manipulating DATE values. (See the Informix Guide to SQL: Syntax.)

The DATE data type is compact, at 4 bytes per item. Arithmetic functions and comparisons execute quickly on a DATE column.

GLS

Choosing a date format

10/25/95

For languages other than English, you can also change the date format by means of the TIME category of the locale file. For more information on using locales, refer to the Guide to GLS Functionality.

Exact Points in Time: DATETIME

The advantage of DATETIME is that it can store dates more precisely than to the nearest day, and it can store time values. Its sole disadvantage is an inflexible display format, but you can circumvent this advantage. (See "Forcing the format of a DATETIME or INTERVAL value".)

Figure 9-3 Precisions for the DATETIME Data Type

Durations: INTERVAL

• An employee began working on January 21, 1994 (either a DATE or a DATETIME).
• She has worked for 254 days (an INTERVAL value, the difference between the TODAY function and the starting DATE or DATETIME value).
• She begins work each day at 0900 hours (a DATETIME value).
• She works 8 hours (an INTERVAL value) with 45 minutes for lunch (another INTERVAL value).
• Her quitting time is 1745 hours (the sum of the DATETIME when she begins work and the two INTERVALs).

INTERVAL values can be negative as well as positive. You can add or subtract them, and you can scale them by multiplying or dividing by a number. This is not true of either DATE or DATETIME. You can reasonably ask, "What is one-half the number of days until April 23?" but not, "What is one-half of April 23?"

Forcing the format of a DATETIME or INTERVAL value

You can write a SELECT statement that displays the date part of a DATETIME value in the system-defined format. The trick is to isolate the component fields using the EXTEND function and pass them through the MDY() function, which converts them to a DATE. The following code shows a partial example:

SELECT ... MDY (

	EXTEND (DATE_RECEIVED, MONTH TO MONTH),

	EXTEND (DATE_RECEIVED, DAY TO DAY),

	EXTEND (DATE_RECEIVED, YEAR TO YEAR))

	FROM RECEIPTS ...

1995-10-25 18:02:13

To customize this DATETIME format, choose your locale appropriately or set the GL_DATETIME or DBTIME environment variable. For more information, see the Guide to GLS Functionality.

Boolean Data Type

The following table shows how the BOOLEAN data type is represented.

BOOLEAN Representation	Internal Representation	Literal Representation
TRUE	\1	't', 'T'
FALSE	\0	'f', 'F'
NULL	For internal use only	NULL

You can compare a BOOLEAN column against another BOOLEAN column, or against Boolean values ('t','f'). For example, suppose you create the following table:

CREATE TABLE emp_info
(
	emp_id INTEGER,
	bool_col BOOLEAN
);

SELECT * 
	FROM emp_info 
	WHERE bool_col = 't'; 

SELECT * 
	FROM emp_info 
	WHERE bool_col IS NULL; 

UPDATE emp_info
	SET bool_col = (1 < 2) 
	WHERE emp_id = 439

Character Data: CHAR(n) and NCHAR(n)

Data in CHAR columns is sorted in code-set order. For example, in the ASCII code set, the character a has a code-set value of 97, b has 98, and so forth. The database server sorts CHAR(n) data in this order.

The NCHAR(n) data type also contains a sequence of n bytes. These characters can be a mixture of English and non-English characters and can be either single byte or multibyte (Asian). The length of n has the same limits as the CHAR(n) data type. Whenever an NCHAR(n) value is retrieved or stored, exactly n bytes are transferred. The number of characters transferred can be less than the number of bytes if the data contains multibyte characters. If an inserted value is shorter than n, the database server extends the value by using single byte ASCII space characters to make up n bytes.

Tip: The database server accepts values from the user that are extended with either single-byte or multibyte spaces as the locale defines.

The database server sorts data in NCHAR(n) columns according to the order that the locale specifies. For example, the French locale specifies that the character ê is sorted after the value e but before the value f. In other words, the sort order dictated by the French locale is e, ê, f, and so on. For more information on using locales, refer to the Guide to GLS Functionality.

Tip: The only difference between CHAR(n) and NCHAR(n) data is the data sorting and comparison. You can store non-English characters in a CHAR(n) column. However, because the database server uses code-set order to perform any sorting or comparison on CHAR(n) columns, you might not obtain the results in the order that you expected.

A CHAR(n) or NCHAR(n) value can include tabs and spaces but normally contains no other nonprinting characters. When rows are inserted using INSERT or UPDATE, or when rows are loaded with a utility program, no means exists for entering nonprintable characters. However, when rows are created by a program using embedded SQL, the program can insert any character except the null (binary zero) character. It is not a good idea to store nonprintable characters in a character column because standard programs and utilities do not expect them.

The advantage of the CHAR(n) or NCHAR(n) data type is its availability on all database servers. The only disadvantage of CHAR(n) or NCHAR(n) is its fixed length. When the length of data values varies widely from row to row, space is wasted.

Varying-Length Strings: CHARACTER VARYING(m,r), VARCHAR(m,r), NVARCHAR(m,r), and LVARCHAR

Tip: The data type CHARACTER VARYING (m,r) is ANSI compliant. VARCHAR(m,r) is an Informix data type.

Often the items in a character column have different lengths; that is, many have an average length, and only a few have the maximum length. The following data types are designed to save disk space when you store such data:

• CHARACTER VARYING (m,r). The CHARACTER VARYING (m,r) data type contains a sequence of, at most, m bytes or at the least, r bytes. This data type is the ANSI-compliant format for character data of varying length. CHARACTER VARYING (m,r) supports code-set order for comparisons of its character data.
• VARCHAR (m,r). VARCHAR (m,r) is an Informix-specific data type for storing character data of varying length. In functionality, it is the same as CHARACTER VARYING(m,r).
• NVARCHAR (m,r). NVARCHAR (m,r) is also an Informix-specific data type for storing character data of varying length. It compares character data in the order that the locale specifies.
• LVARCHAR. LVARCHAR is an Informix-specific data type for storing character data of varying length for values greater than 256 bytes but less than 32 kilobytes. LVARCHAR supports code-set order for comparisons of its character data

Tip: The difference in the way data is compared distinguishes NVARCHAR(m,r) data from CHARACTER VARYING(m,r) or VARCHAR(m,r) data. For more information on code set and sort order determined by the locale, see "Character Data: CHAR(n) and NCHAR(n)".

The second parameter, r, is an optional reserve length that sets a lower limit on the number of bytes required by the value that is being stored on disk. Even if a value requires fewer than r bytes, r bytes are nevertheless allocated to hold it. The purpose is to save time when rows are updated. (See "Varying-Length Execution Time".)

The advantages of the CHARACTER VARYING(m,r) or VARCHAR(m,r) data type over the CHAR(n) data type are as follows:

• It conserves disk space when the number of bytes that data items require vary widely or when only a few items require more bytes than average.
• Queries on the more compact tables can be faster.

The following list describes the disadvantages of using CHARACTER VARYING(m,r), VARCHAR(m,r), and NVARCHAR(m,r) data types:

• They do not allow lengths that exceed 255 bytes.
• Table updates can be slower in some circumstances.
• They are not available with all Informix database servers.

Because more rows fit in a disk page, the database server can search the table with fewer disk operations than if the rows were of a fixed number of bytes. As a result, queries can execute more quickly. Insert and delete operations can be a little quicker for the same reason.

When you update a row, the amount of work the database server must do depends on the number of bytes in the new row as compared with the number of bytes in the old row. If the new row uses the same number of bytes or fewer, the execution time is not significantly different than it is with fixed-length rows. However, if the new row requires a greater number of bytes than the old one, the database server might have to perform several times as many disk operations. Thus, updates of a table that use CHARACTER VARYING(m,r), VARCHAR(m,r), or NVARCHAR(m,r) data can sometimes be slower than updates of a fixed-length field.

To mitigate this effect, specify r as a number of bytes that covers a high proportion of the data items. Then most rows use the reserve number of bytes, and padding wastes only a little space. Updates are slow only when a value using the reserve number of bytes is replaced with a value that uses more than the reserve number of bytes.

Large Object Data Types

Smart large objects refer to columns that are assigned a BLOB or CLOB data type. A smart large object allows an application program to randomly access column data, which means you can read or write to any part of a BLOB or CLOB column in any arbitrary order.

Simple large objects refer to columns that are assigned a TEXT or BYTE data type. A simple large object can store and retrieve character data or binary data, but cannot randomly access portions of the column data. In other words, TEXT or BYTE data can be inserted or deleted but cannot be modified. The database server simply stores or retrieves the TEXT or BYTE data in a single SQL statement.

The following sections describe additional differences between simple large objects and smart large objects.

Smart Large Objects: CLOB

CLOB values are not stored with the rows of which they are a part. They are allocated in whole disk pages, usually areas away from rows. (For more information, see the INFORMIX-Universal Server Administrator's Guide.)

The CLOB data type is similar to the TEXT data type except that the CLOB data type provides the following advantages:

• An application program can read from or write to any portion of the CLOB object.
• Access times can be significantly faster because an application program can access any portion of a CLOB object.
• Default characteristics are relatively easy to override. Database administrators can override default characteristics for sbspace at the column level. Application programmers can override some default characteristics for the column when they create a CLOB object.
• You can use the equals operator (=) to test whether two CLOB values are equal.
• A CLOB object is recoverable in the event of a system crash and obeys transaction isolation modes (when specified by the DBA or application programmer). (Recovery of CLOB objects requires that your database system has the necessary resources to provide buffers large enough to handle CLOB objects.)
• You can use the CLOB data type to provide large storage for a user-defined data type.
• DataBlade developers can create indexes on CLOB data types.

• It is allocated in whole disk pages, so a short item wastes space.
• Restrictions apply on how you can use a CLOB column in an SQL statement. (See "Using Smart Large Objects".)
• It is not available with all Informix database servers.

BLOB data items are stored in whole disk pages in separate disk areas from normal row data.

The advantage of the BLOB data type, as opposed to CLOB, is that it accepts any data. Otherwise, the advantages and disadvantages of the BLOB data type are the same as for the CLOB data type.

Using Smart Large Objects

In any SQL statement, interactive or programmed, a CLOB or BLOB column cannot be used in the following ways:

• In arithmetic or Boolean expressions
• In a GROUP BY or ORDER BY clause
• In a UNIQUE test
• For indexing, as part of an Informix B+ tree index

In a SELECT statement entered interactively, a CLOB or BLOB column can:

• specify null values as a default when you create a table with the DEFAULT NULL clause.
• disallow null values using the NOT NULL constraint when you create a table.
• be tested with the IS [NOT] NULL predicate.

Important: Casts between CLOB and BLOB data types are not permitted.

Copying smart large objects

Figure 9-5
SQL Functions for Smart Large Objects

Function Name	Purpose
FILETOBLOB()	Copies a file into a BLOB column.
FILETOCLOB()	Copies a file into a CLOB column.
LOCOPY()	Copies BLOB or CLOB data into another BLOB or CLOB column.
LOTOFILE()	Copies a BLOB or CLOB into a file.

You can use any of the functions that Figure 9-5 shows in the SELECT, UPDATE, and INSERT statements. (The following examples assume that the SBSPACENAME parameter has been specified as sbspace1.)

Suppose you create the following inmate and fbi_list tables:

CREATE TABLE inmate 
(
	id_num 		INT, 
	picture		BLOB,
	felony 		CLOB
);


CREATE TABLE fbi_list 
( 
	id 		INTEGER,
	mugshot		 BLOB
) PUT mugshot IN (sbspace1);

INSERT INTO inmate
VALUES (437, FILETOBLOB('datafile', 'client'), 
		FILETOCLOB('tmp/text', 'server'))

• If the source file resides on the server computer, you must specify the full pathname to the file (not the pathname relative to the current working directory).
• If the source file resides on the client computer, you can specify either the full or relative pathname to the file.

UPDATE inmate (picture)
	SET picture = LOCOPY(mugshot, 'fbi_list', 'mugshot')
	WHERE inmate.id_num = 437 AND fbi_list.id = 669;

The following SELECT statement uses the LOTOFILE() function to copy data from the felony column into the felon_322.txt file that is located on the client computer:

SELECT id_num, LOTOFILE(felony, 'felon_322.txt', 'client')
FROM inmate
WHERE id = 322

Inheritance of characteristics for smart large objects

ESQL/C programs that access CLOB or BLOB data from a row can override some column-level defaults at the time that the program creates a BLOB or CLOB instance. For information about how to override column-level defaults when you create an instance of a smart large object from an ESQL/C program, see the INFORMIX-ESQL/C Programmer's Manual.

Figure 9-6 shows the precedence rules that Universal Server uses to determine which characteristics a smart large object inherits.

Figure 9-6 Precedence Rules That Determine How a Smart Large Object Inherits Characteristics

For information about Universal Server defaults and sbspace defaults, see the INFORMIX-Universal Server Administrator's Guide.

Simple Large Objects: TEXT

TEXT values are not stored with the rows of which they are a part. They are allocated in whole disk pages, usually in areas away from rows. (See the INFORMIX-Universal Server Administrator's Guide.)

The advantage of the TEXT data type over CHAR(n) and VARCHAR(m,r) is that the size of a TEXT data item has no limit except the capacity of disk storage to hold it. The disadvantages of the TEXT data type are as follows:

• You cannot write to a portion of a TEXT column. (However, you can read from and write to any portion of a CLOB column.)
• It is allocated in whole disk pages, so a short item wastes space.
• Restrictions apply on how you can use a TEXT column in an SQL statement. (See "Using Simple Large Objects".)
• A system crash under certain circumstance can result in a loss of data.
• Overriding default characterisitcs for TEXT columns can be a time-intensive task (in comparison with CLOB columns).
• It is not available with all Informix database servers.

Simple Large Objects: BYTE

As with TEXT, BYTE data items are stored in whole disk pages in separate disk areas from normal row data.

The advantage of the BYTE data type, as opposed to TEXT or CHAR(n), is that it accepts any data. Its disadvantages are the same as those of the TEXT data type.

Using Simple Large Objects

In any SQL statement, interactive or programmed, a TEXT or BYTE column cannot be used in the following ways:

• In arithmetic or Boolean expressions
• In a GROUP BY or ORDER BY clause
• In a UNIQUE test
• For indexing, either by itself or as part of a composite index

• be selected by name, optionally with a subscript to extract part of it.
• have its length returned by selecting LENGTH(column).
• be tested with the IS [NOT] NULL predicate.

In an interactive UPDATE statement, you can update a simple-large-object column to null or to a subquery that returns a simple-large-object column.

Changing the Data Type

• To change a data type, the database server must copy and rebuild the table. For large tables, copying and rebuilding can take a lot of time and disk space.
• Some data type changes can cause a loss of information. For example, when you change a column from a longer to a shorter character type, long values are truncated; when you change to a less-precise numeric type, low-order digits are truncated.
• Existing programs, forms, reports, and stored queries might also have to be changed.

Null Values

INSERT INTO manufact VALUES ('DRM', 'Drumm', NULL)

Default Values

• USER
• CURRENT
• TODAY
• DBSERVERNAME

Check Constraints

Customer_Number >= 50000 AND Customer_Number <= 99999

vce_num MATCHES '[2-9][2-9][0-9]-[0-9][0-9][0-9][0-9]'

Domains

You cannot use the CREATE DOMAIN statement to create an alias for the following data types:

• User-defined opaque data types
• User-defined distinct data types
• BOOLEAN
• CLOB
• BLOB

SET(VARCHAR(30) NOT NULL)

CREATE DOMAIN d_employee AS SET(VARCHAR(30) NOT NULL)

CREATE TABLE department (dept_num INT, employees d_employee);

Dropping a Domain

DROP DOMAIN d_employee

DROP DOMAIN d_employee;
SELECT employees FROM department;
CREATE DOMAIN d_employee SET(INTEGERS) NOT NULL);
SELECT employees FROM department;

For more information, see the description of the DROP DOMAIN statement in the Informix Guide to SQL: Syntax.

To change the data type of a column defined on a domain

2. Create a new domain

3. Use the ALTER TABLE statement to modify the column data type

DROP DOMAIN d_employee;
CREATE DOMAIN d_employee AS SET(INTEGER NOT NULL);
ALTER TABLE department MODIFY employees d_employee;