Defining Columns

Go Up to Creating Tables (Data Definition Guide)

When you create a table in the database, your main task is to define the various attributes and constraints for each of the columns in the table. The syntax for defining a column is:

<col_def> = col {data_type
| COMPUTED [BY] (<expr>)
| domain}
 [DEFAULT {literal | NULL | USER}]
 [NOT NULL] [<col_constraint>]
 [COLLATE collation]

The next sections list the required and optional attributes that you can define for a column.

Required Attributes

You are required to specify:

• A column name, which must be unique among the columns in the table.
• One of the following:

• A SQL data type (<data_type>).
• An expression (<expr>) for a computed column.
• A domain definition (<domain>) for a domain-based column.

Optional Attributes

You have the option to specify:

• A default value for the column.
• Integrity constraints. Constraints can be applied to a set of columns (a table-level constraint), or to a single column (a column-level constraint). Integrity constraints include:

• The PRIMARY KEY column constraint, if the column is a PRIMARY KEY, and the PRIMARY KEY constraint is not defined at the table level. Creating a PRIMARY KEY requires exclusive database access.
• The UNIQUE constraint, if the column is not a PRIMARY KEY, but should still disallow duplicate and NULL values.
• The FOREIGN KEY constraint, if the column references a PRIMARY KEY in another table. Creating a FOREIGN KEY requires exclusive database access. The foreign key constraint includes the ON UPDATE and ON DELETE mechanisms for specifying what happens to the foreign key when the primary key is updated (cascading referential integrity).

• A NOT NULL attribute does not allow NULL values. This attribute is required if the column is a PRIMARY KEY or UNIQUE key.
• A CHECK constraint for the column. A CHECK constraint enforces a condition that must be true before an insert or an update to a column or group of columns is allowed.
• A CHARACTER SET can be specified for a single column when you define the data type. If you do not specify a character set, the column assumes the database character set as a default.

Specifying the Data Type

When creating a table, you must specify the data type for each column. The data type defines the set of valid data that the column can contain. The data type also determines the set of allowable operations that can be performed on the data, and defines the disk space requirements for each data item.

Syntax

<data type> =
 {SMALLINT|INTEGER|FLOAT|DOUBLE PRECISION} [<array_dim>]
 | {DATE|TIME|TIMESTAMP} [<array_dim>]
 | {DECIMAL | NUMERIC} [(precision [, scale])] [<array_dim>]
 | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)][<array_dim>] [CHARACTER SET charname]
 | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}[VARYING] [(int)] [<array_dim>]
 | BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int][CHARACTER SET charname]
 | BLOB [(seglen [, subtype])]
 | BOOLEAN

<array_dim> = [x:y [, x1:y1 ...]]

Note:
<subtype_name> can be a TEXT value.

Note:
The outermost brackets must be included when declaring arrays.

Supported data types

The general categories of data types that are supported include:

• Character data types.
• Integer data types.
• Decimal data types, both fixed and floating.
• A DATE data type to represent the date, a TIME data type to represent the time, and a TIMESTAMP data type to represent both the date and time.
• A BOOLEAN data type.
• A BLOB data type to represent unstructured binary data, such as graphics and digitized voice.
• Arrays of data types (except for BLOB data).

See About InterBase Data Types for a complete list and description of data types that InterBase supports.

Casting Data Types

If your application programming language does not support a particular data type, you can let InterBase automatically convert the data to an equivalent data type (an implicit type conversion), or you can use the CAST() function in search conditions to explicitly translate one data type into another for comparison purposes. For more information about specifying data types and using the CAST() function, see Specifying Data Types.

Defining a Character Set

The data type specification for a CHAR, VARCHAR, or BLOB text column definition can include a CHARACTER SET clause to specify a particular character set for a column. If you do not specify a character set, the column assumes the default database character set. If the database default character set is subsequently changed, all columns defined after the change have the new character set, but existing columns are not affected. For a list of available character sets recognized by InterBase, see Character Sets and Collation Orders.

The COLLATE Clause

The collation order determines the order in which values are sorted. The COLLATE clause of CREATE TABLE allows you to specify a particular collation order for columns defined as CHAR and VARCHAR text data types. You must choose a collation order that is supported for the given character set of the column. The character set is either the default character set for the entire database, or you can specify a different set in the CHARACTER SET clause of the data type definition. The collation order set at the column level overrides a collation order set at the domain level.

In the following statement, BOOKNO keeps the default collating order for the default character set of the database. The second (TITLE) and third (EUROPUB) columns specify different character sets and collating orders.

CREATE TABLE BOOKADVANCE (BOOKNO CHAR(6),
 TITLE CHAR(50) CHARACTER SET DOS437 COLLATE PDOX_INTL,
 EUROPUB CHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR);

For a list of the available characters sets and collation orders that InterBase recognizes, see Character Sets and Collation Orders.

Defining Domain-based Columns

When you create a table, you can set column attributes by using an existing domain definition that has been previously stored in the database. A domain is a global column definition. Domains must be created with the CREATE DOMAIN statement before you can reference them to define columns locally. For information on how to create a domain, see Working with Domains.

Domain-based columns inherit all the characteristics of a domain, but the column definition can include a new default value, additional CHECK constraints, or a collation clause that overrides the domain definition. It can also include additional column constraints. You can specify a NOT NULL setting if the domain does not already define one.

Note:
You cannot override the domain’s NOT NULL setting with a local column definition.

For example, the following statement creates a table, COUNTRY, referencing the domain, COUNTRYNAME, which was previously defined with a data type of VARCHAR(15):

CREATE TABLE COUNTRY
(COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
 CURRENCY VARCHAR(10) NOT NULL);

Defining Expression-based Columns

A computed column is one whose value is calculated each time the column is accessed at run time. The syntax is:

<col_name> COMPUTED [BY] (<expr>);

If you do not specify the data type, InterBase calculates an appropriate one. <expr> is any arithmetic expression that is valid for the data types in the columns; it must return a single value, and cannot be an array or return an array. Columns referenced in the expression must exist before the COMPUTED [BY] clause can be defined.

For example, the following statement creates a computed column, FULL_NAME, by concatenating the LAST_NAME and FIRST_NAME columns.

CREATE TABLE EMPLOYEE
(FIRST_NAME VARCHAR(10) NOT NULL,
 LAST_NAME VARCHAR(15) NOT NULL,
 FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME));

The next example creates a table with a calculated column (NEW_SALARY) using the previously created EMPNO and SALARY domains.

CREATE TABLE SALARY_HISTORY (EMP_NO EMPNO NOT NULL,
CHANGE_DATE DATE DEFAULT 'NOW' NOT NULL,
UPDATER_ID VARCHAR(20) NOT NULL,
OLD_SALARY SALARY NOT NULL,
PERCENT_CHANGE DOUBLE PRECISION
DEFAULT 0
NOT NULL
CHECK (PERCENT_CHANGE BETWEEN –50 AND 50),
NEW_SALARY COMPUTED BY
(OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO)
ON UPDATE CASCADE
ON DELETE CASCADE);

Note:
Constraints on computed columns are not enforced, but InterBase does not return an error if you do define such a constraint.

Specifying Column Default Values

You can set an optional default value that is automatically entered into a column if you do not specify an explicit value. Defaults set at the column level with CREATE TABLE or ALTER TABLE override defaults set at the domain level. Defaults can save data entry time and prevent data entry errors. For example, a possible default for a DATE column could be today’s date, or in a (Y/N) flag column for saving changes, “Y” could be the default.

Default values can be:

• <literal>—The default value is a user-specified string, numeric value, or date value.
• NULL—If the user does not enter a value, a NULL value is entered into the column.
• USER—The default is the name of the current user. If your operating system supports the use of 8- or 16-bit characters in user names, then the column into which USER will be stored must be defined using a compatible character set.

In the following example, the first statement creates a domain with USER named as the default. The next statement creates a table that includes a column, ENTERED_BY, based on the USERNAME domain.

CREATE DOMAIN USERNAME AS VARCHAR(20)
 DEFAULT USER;
CREATE TABLE ORDERS (ORDER_DATE DATE, ENTERED_BY USERNAME,

 ORDER_AMT DECIMAL(8,2));
INSERT INTO ORDERS (ORDER_DATE, ORDER_AMT)
 VALUES ('1-MAY-93', 512.36);

The INSERT statement does not include a value for the ENTERED_BY column, so InterBase automatically inserts the user name of the current user, JSMITH:

SELECT * FROM ORDERS;

Specifying NOT NULL Columns

You can optionally specify NOT NULL to force the user to enter a value. If you do not specify NOT NULL, then NULL values are allowed in the column. You cannot override a NOT NULL setting that has been set at a domain level with a local column definition.

Note:
If you have already specified NULL as a default value, be sure not to create contradictory constraints by also specifying the NOT NULL attribute, as in the following example:

CREATE TABLE MY_TABLE (COUNT INTEGER DEFAULT NULL NOT NULL);

Advance To:

• Defining Integrity Constraints on a Table