SQL Column and Index Definitions

This topic describes the purpose of each of the fields in column and index definitions. Values can be set using SQLM.

SQL Column Definition Fields

Identifies the name of the column as it must be shown in an SQL query. Case is significant. Refer to the topic Naming of Tables, Columns and Indexes.

Identifies the data type that this column may contain. Reality attributes do not have types, but SQL needs some information about the type of data that may occur in an attribute. Valid type names and numbers are:

Type name

Type number

Description

BIT

-7

Single bit binary data.

TINYINT

-6

 Integer in the range: -128 to 127.

BIGINT

-5

Integer in the range: -263 to 263 - 1.

LONGVARBINARY

-4

Variable length binary data (any length).

VARBINARY

-3

Variable length binary data (1 to 255 bytes).

BINARY

-2

Fixed length binary data (1 to 255 bytes).

LONGVARCHAR

-1

General data of any length.

CHAR

1

General data of fixed length (1<=length<=254).

NUMERIC

2

Numeric data including fractional digits.

DECIMAL

3

Numeric data including fractional digits.

INTEGER

4

Integer in the range: -2,147,483,648 to 2,147,483,647.

SMALLINT

5

Integer in the range: -32,768 to +32,767.

FLOAT

6

Signed, approximate numeric value with a mantissa precision of 15 (10-308 to 10308).

REAL

7

Signed, approximate numeric value with a mantissa precision of 7 (10-38 to 1038).

DOUBLE

8

Signed, approximate numeric value with a mantissa precision of 15 (10-308 to 10308).

DATE

9

Date stored in Reality internal format.

TIME

10

Time stored in Reality internal format.

TIMESTAMP

11

Timestamp stored in Reality internal format.

VARCHAR

12

General data of up to 254 characters.

 

Defines the column width or, for VAR types, the maximum length of the string. This field should be set according to column type as follows:

Date type

Setting

BIGINT

Always set to 19.

BINARY

The number of bytes (255 maximum).

BIT

Always set to 1.

CHAR

The length of the string (254 characters maximum).

DATE

Always set to 10 (for ODBC format ?yyyy-mm-dd?).

DECIMAL

The maximum number of digits (including those after the decimal point, but not including the decimal point or sign).

DOUBLE

Always set to 15.

FLOAT

Always set to 15.

INTEGER

Always set to 10 (allows integers in the range: -2147483648 to +2147483647).

LONGVARBINARY

The maximum number of bytes.

LONGVARCHAR

The maximum length of the string.

NUMERIC

The maximum number of digits (including those after the decimal point, but not including the decimal point or sign).

REAL

Always set to 7.

SMALLINT

Always set to 5.

TIME

Always set to 8 (for ODBC format 'hh:mm:ss').

TIMESTAMP

Always set to 19 (for ODBC format 'yyyy-mm-dd hh:mm:ss').

TINYINT

Always set to 3.

VARBINARY

The maximum number of bytes (255 maximum).

VARCHAR

The maximum length of the string (254 characters maximum).

 

Defines the number of digits to the right of the decimal point for DECIMAL and NUMERIC column types. Not applicable to other column types.

Defines attribute characteristics. Can be one of:

n The number of the attribute in the Reality item to be used as the source for this column.

0 Generate column based on item-id, or derive values indirectly (see Data Generation).

E Explode - where items contain a list of elements of the same type (such as item-ids or text strings) in different attributes, ?explode? places each element in a separate row in the SQL table. This is a method of dealing with Reality Select (open-ended) lists.

NA Populate this column with virtual data derived from the current attribute number in a table that has exploded attributes. To use NA, the column must be of the TINYINT, SMALLINT or INTEGER type.

Note

For an explanation of exploded attributes, see E above.

NV Populate this column with virtual data derived from the current multivalue number in a table that has exploded multivalues. To use NV, the column must be of the TINYINT, SMALLINT or INTEGER type.

NS Populate this column with virtual data derived from the current subvalue number in a table that has exploded subvalues. To use NS, the column must be of the TINYINT, SMALLINT or INTEGER type.

Code specifying action when this attribute is multivalued (contains values separated by value marks, X?FD?, ctrl+]). Can be one of:

Null Use first multivalue only.

0 Use all multivalues, separated by spaces.

n Use the nth multivalue (where n is a positive integer).

E Explode - generate a separate row for each multivalue.

-1 Use the last multivalue.

The selected value of any non-exploded column is repeated for each additional row generated for an exploded column.

Code specifying action when this attribute contains subvalues (values separated by subvalue marks, X?FC?, ctrl+\). Can be one of:

Null Use first subvalue only.

0 Use all subvalues, separated by spaces.

n Use the nth subvalue (where n is a positive integer).

E Explode - generate a separate row for each subvalue.

-1 Use the last subvalue.

The selected value of any non-exploded column is repeated for each additional row generated for an exploded column.

Conversion code. Converts data read from disk to internal format. It is applied to the stored values before any other processing. See Conversions in an SQL Column Definition.

In most cases, SQLM copies the pre-processor conversion code (attribute 8 of the existing data definition item) into this field. The field is left null if there are no such codes.

Note, however, the following:

  • A codes (other than the simple An, which applies a scaling factor in the range 1 to 6) are not valid, but are converted to equivalent F codes before being inserted in this field.
  • Date codes are not copied, as column type 9 (DATE) should be used, which causes the internal date representation to be automatically converted on retrieval from file to standard format (yyyy-mm-dd).
  • Translate conversions (of form Tfilespec, etc.) are valid for data generation. They allow data to be extracted from a file other than that on which the table is based, by using values from the first file as item-ids in the 'translate' file. These may sometimes be more convenient or efficient than using the SQL JOIN construct.
  • SQLM cannot convert times and combined dates and times.

Note

This field is not used when a remote application executes an SQL statement via the ODBC or JDBC interface.

Converts data in internal format to a defined output format for display on a terminal screen, or output to a printer. See also Conversions in an SQL Column Definition.

SQLM copies the output conversion code (attribute 7 of the existing data definition item) into this field. The field is left null if there is no such code.

Note

This field is not used when a remote application executes an SQL statement via the ODBC or JDBC interface.

Converts data to internal format from external format, as input at the keyboard. See also Conversions in an SQL Column Definition.

SQLM copies any output conversion code (attribute 7 of the existing data definition item) into this field. The field is left null if there is no such code.

Input processing is normally the inverse of the output conversion code defined in field 9, in which case you can enter = in this field which tells the SQL command to use the same code as field 9.

Converts data to stored format immediately before it is written to disk. See also Conversions in an SQL Column Definition.

Update conversion is normally the inverse of Data Generation, in which case you can enter = in this field which tells the SQL command to use the same code as field 8. SQLM copies the codes from attribute 8 of the existing data definition item directly into this field, except as noted in the description of the Data Generation field.

Not used except in SQL statements executed from TCL, when it is used as the column width. For date and time columns, the column width should be set to the following:

Column type

Column width

Date

10

Time

8

Timestamp

19

SQLM copies attribute 10 (column width) from the data definition item into this field, unless column type is 9 (date), when '10' is used. SQLM cannot convert times and combined dates and times.

Indicates whether this column can contain null values (Y = yes, N = no).

Indicates, if Y is shown, that this column is the primary key, or part of the primary key, for the table.

The primary key is a column, or a set of columns, that contain values that uniquely identify the records in a table. This may be a unique identification number, such as an employee id or a serial number.

Values in a primary key field must be both unique and nonnullable. In Reality, it is almost invariably logical to set the item-id as the primary key. Therefore, for columns derived from attribute 0, SQLM sets this field to Y, sets field 13 (Null ) to N and field 15 (Unique) to Y.

The primary key can, if appropriate (such as when the Reality item-id consists of components defined as two or more SQL columns), comprise two or more concatenated columns. To define such a primary key, use SQLM Option 4 (Amend Table Definitions), sub-option 2 (Update primary keys).

Indicates whether values in this column must be unique, apart from nulls, of which there can be any number.

N Values in this column need not be unique.

SIMPLE Values in this column must be unique.

COMPLEX The combination of values in this column and those in one or more other columns must be unique.

If Y is shown, only a special character string (by default %%) is treated as a null (that is, an unknown quantity) for SQL purposes. Otherwise, an 'empty' value (that is, the absence of a value) is treated as a null. This field is usually set to N.

If you set this field to Y, you are prompted to enter the special character string that signifies null, or to accept the default string (%%). The null string chosen is used for the whole database.

Can contain additional information about the column.