Hitachi

Hitachi Advanced Database SQL Reference


3.7.1 Specification format and rules for the CREATE TABLE statement

The CREATE TABLE statement defines a base table.

Organization of this subsection

(1) Specification format

CREATE-TABLE-statement ::=
     CREATE [FIX] TABLE table-name(table-element[,table-element]...)
       [IN DB-area-name]
       [PCTFREE=percentage-of-unused-area]#
       [BRANCH ALL]#
       [chunk-specification]#
       [STORAGE FORMAT {ROW|COLUMN}]#
 
 
  table-element ::= {column-definition|table-constraint}
 
    column-definition ::= column-name data-type [DEFAULT-clause] [NOT NULL] [BRANCH {YES|NO|AUTO}]
                              [compression-type-specification]
      DEFAULT-clause ::= DEFAULT default-option
        default-option ::= {literal|CURRENT_DATE|CURRENT_TIME[(p)]
                          |CURRENT_TIMESTAMP[(p)]|CURRENT_USER|NULL}
 
      compression-type-specification ::= COMPRESSION TYPE {AUTO|NONE|RUNLENGTH|DICTIONARY|DELTA
                                         |DELTA_RUNLENGTH}
 
 
    table-constraint ::= {uniqueness-constraint-definition|referential-constraint-definition}
      uniqueness-constraint-definition ::= [CONSTRAINT constraint-name] PRIMARY KEY (column-name [{ASC|DESC}]
                                [,column-name [{ASC|DESC}]]...)
                                [IN DB-area-name]
                                [PCTFREE=percentage-of-unused-area]
 
      referential-constraint-definition ::= [CONSTRAINT constraint-name] FOREIGN KEY (column-name[,column-name]...)
                                 REFERENCES table-name DISABLE
 
 
  chunk-specification ::= CHUNK[=maximum-number-of-chunks]
                         [chunk-archive-specification]
    chunk-archive-specification ::= ARCHIVABLE
                                  RANGECOLUMN=column-name
                                 [RANGEINDEXNAME=index-identifier]
                                 [IN DB-area-name]
                                  ARCHIVEDIR=archive-directory-name
#

PCTFREE, BRANCH ALL, chunk-specification, and STORAGE FORMAT can be specified in any order.

Note

PCTFREE, BRANCH ALL, chunk-specification, and STORAGE FORMAT are referred to collectively as table options.

The following table lists the options for defining row store tables and the options for defining column store tables.

Table 3‒7: Options for defining row store tables or column store tables

No.

CREATE TABLE option

For defining row store tables

For defining column store tables

1

FIX

Y

N

2

table-name

Y

Y

3

column-definition

column-name

Y

Y

4

data-type

Y

Y

5

DEFAULT-clause

Y

Y

6

NOT NULL

Y

Y

7

BRANCH

Y

N

8

compression-type-specification

N

Y

9

table-constraint

Y

Y

10

IN DB-area-name

Y

Y

11

PCTFREE

Y

N

12

BRANCH ALL

Y

N

13

chunk-specification

maximum-number-of-chunks

Y

Y

14

chunk-archive-specification

Y

N

15

STORAGE FORMAT

Y

Y

Legend:

Y: An option that can be specified, or one that must be specified.

N: An option that cannot be specified.

(2) Explanation of specification format

In the option descriptions, options marked [Row store table] can be specified to define a row store table. Options marked [Column store table] can be specified to define a column store table. Options marked [Common] can be specified to define both a row store table and a column store table.

(a) FIX [Row store table]

Defines a base table in which every row has a fixed length (a FIX table).

The following rules apply:

  • If FIX is specified, the following data type cannot be specified for any row in this table:

    • VARCHAR

    • VARBINARY

  • If FIX is specified, NOT NULL constraint is set for all columns of the base table.

  • Only FIX tables allow you to perform reference, update, and insert operations by row (ROW specification).

  • For archivable multi-chunk tables, FIX cannot be specified.

(b) table-name [Common]

Specifies the name of the base table to be defined. You cannot specify the table name of a table that has already been defined. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

(c) table-element [Common]

table-element ::= {column-definition | table-constraint}

A table element specifies either a column definition or a table constraint.

(d) column-definition [Common]

column-definition ::= column-name data-type [DEFAULT-clause] [NOT NULL] [BRANCH {YES | NO | AUTO}]
                          [compression-type-specification]

Specifies the definitions of the columns that make up the base table. At least one column definition must be specified.

column-name [Common]

Specifies the names of the columns that comprise the table. Each column name must be unique.

Do not specify a character string in the EXPnnnn_NO_NAME format as a column name. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.

data-type [Common]

Specifies the data types of the columns. The following table lists the data types that can be specified.

Table 3‒8: Data types that can be specified (CREATE TABLE statement)

No.

Data type

Specification format

1

INTEGER

INT or INTEGER

2

SMALLINT

SMALLINT

3

DECIMAL

DEC[(m[,n])] or DECIMAL[(m[,n])]

m: Precision (total number of digits)

n: Scaling (number of decimal places)

If m is omitted, 38 is assumed, and if n is omitted, 0 is assumed.

4

DOUBLE PRECISION

DOUBLE or DOUBLE PRECISION

5

CHARACTER

CHAR(n) or CHARACTER(n)

n: Length of character string (in bytes)

If CHAR or CHARACTER is specified without a length, the length of the character string is assumed to be 1.

6

VARCHAR

VARCHAR(n)

n: Maximum length of character string (in bytes)

7

DATE

DATE

8

TIME

TIME(p) or TIME

p: Fractional seconds precision (number of digits to the right of the decimal point)

You can specify a value of 0, 3, 6, 9, or 12 for p. If TIME is specified, p is assumed to be 0.

9

TIMESTAMP

TIMESTAMP(p) or TIMESTAMP

p: Fractional seconds precision (number of digits to the right of the decimal point)

You can specify a value of 0, 3, 6, 9, or 12 for p. If TIMESTAMP is specified, p is assumed to be 0.

10

BINARY

BINARY(n)

n: Length of the binary data (number of bytes) (number of bytes)

If BINARY is specified without a length, the length of the binary data is assumed to be 1.

11

VARBINARY

VARBINARY(n)

n: Maximum length of the binary data (number of bytes)

For details about data types, see 6.2 Data types.

Important

A VARCHAR-type column whose defined length exceeds 32,000 bytes cannot be specified.

DEFAULT-clause [Common]
DEFAULT-clause ::= DEFAULT default-option
  default-option ::= {literal|CURRENT_DATE|CURRENT_TIME[(p)]
                    |CURRENT_TIMESTAMP[(p)]|CURRENT_USER|NULL}

Specify a DEFAULT clause when you want to set a default value for a column.

For details about the specification format of the DEFAULT clause and the default values for columns, see 7.10 DEFAULT clause.

NOT NULL [Common]

Specify this to define the NOT NULL constraint (the constraint to not allow null values) for the column.

You cannot specify NULL for default-option in the DEFAULT clause for columns on which the NOT NULL constraint is specified.

BRANCH {YES|NO|AUTO} [Row store table]

Specifies how to store VARCHAR-type and VARBINARY-type column data.

For details about situations for which it is better to specify YES or NO for BRANCH, see Branch specification for column data of variable-length data types (BRANCH) [Row store table] in the HADB Setup and Operation Guide.

YES:

Branch the specified VARCHAR-type or VARBINARY-type column data.

NO:

Do not branch the specified VARCHAR-type or VARBINARY-type column data.

AUTO:

Do not branch if the defined length of the specified VARCHAR-type or VARBINARY-type column data is less than or equal to 255 bytes. If the defined length is 256 bytes or greater, branch when the base row does not fit on one page.

If the BRANCH specification is omitted, the system assumes that AUTO is specified.

This option cannot be specified for the following tables and columns:

  • Tables for which the BRANCH ALL table option is specified

  • Columns of data types other than VARCHAR and VARBINARY

compression-type-specification [Column store table]
compression-type-specification ::= COMPRESSION TYPE {AUTO|NONE|RUNLENGTH|DICTIONARY|DELTA|DELTA_RUNLENGTH}

Specifies the compression type to be used to compress the column data in a column store table (column-data compression type). This option can be specified for each column in the column store table.

If compression-type-specification is not specified, the system assumes that AUTO is specified.

AUTO:

If this type is specified, the HADB server automatically determines the data compression type of this column in the column store table.

NONE:

If this type is specified, the data in this column of the column store table is not compressed.

RUNLENGTH:

If this type is specified, the data in this column of the column store table is compressed by using the run-length encoding algorithm.

DICTIONARY:

If this type is specified, the data in this column of the column store table is compressed by using the dictionary encoding algorithm.

DELTA:

If this type is specified, the data in this column of the column store table is compressed by using the delta encoding algorithm.

DELTA_RUNLENGTH:

If this type is specified, the data in this column of the column store table is compressed by using the delta run-length encoding algorithm.

For details about each compression type, see Column-data compression types for column store tables in Criteria for selecting row store tables and column store tables in the HADB Setup and Operation Guide.

(e) table-constraint [Common]

table-constraint ::= {uniqueness-constraint-definition | referential-constraint-definition}

For the table constraint, specify a uniqueness constraint definition or a referential constraint definition.

(f) uniqueness-constraint-definition [Common]

uniqueness-constraint-definition ::= [CONSTRAINT constraint-name] PRIMARY KEY (column-name [{ASC|DESC}]
                          [,column-name [{ASC|DESC}]]...)
                          [IN DB-area-name]
                          [PCTFREE=percentage-of-unused-area]

Specify this if you want to define a primary key for the base table. Only one primary key can be defined for each table.

The uniqueness constraint and NOT NULL constraint are applied to the columns that make up the primary key. The uniqueness constraint disallows duplicate column values (or duplicate combinations of values from multiple columns). The NOT NULL constraint disallows the null value as a column value.

The primary key must be selected from among the columns or combinations of columns that can uniquely identify a row in the table (candidate keys).

Note

The columns or combinations of columns that can uniquely identify a row in the table are referred to as candidate keys.

CONSTRAINT constraint-name [Common]

Specifies a name for the uniqueness constraint definition specified here. For rules on specifying a constraint name, see (2) Rules for characters that can be used in names in 6.1.4 Specifying names.

Note the following rules:

  • If the same constraint name (including referential constraint names) already exists in the same schema, executing the CREATE TABLE statement will result in an error.

  • If this specification is omitted, a name of the following form is generated as the constraint name:

    PRIMARY_nnnnnnnn

    nnnnnnnn: The table ID of the table for which the primary key is to be defined, converted to a character string of eight hexadecimal digits

    If a constraint with the same name as the generated name already exists in the same schema, executing the CREATE TABLE statement will result in an error. It is therefore recommended that you avoid the above format when specifying constraint names (including referential constraint names) or index identifiers.

column-name [Common]

Specify the names of the columns that are to make up the primary key. Note the following rules:

  • A maximum of 16 column names can be specified.

  • If multiple column names are specified, each must be unique.

When the CREATE TABLE statement is executed, it automatically defines a B-tree index as a unique index consisting of the specified columns. This B-tree index is subject to the following rules:

  • If only one column name is specified, a single-column index is defined.

  • If multiple column names are specified, a multiple-column index is defined.

  • The index identifier will be the same as the constraint name.

  • If the index identifier, which is the same as the constraint name, already exists in the same schema, executing the CREATE TABLE statement will result in an error.

Note

The B-tree index that is automatically defined here is subject to the same rules as a B-tree index defined by the CREATE INDEX statement.

{ASC|DESC} [Common]

Specifies the sort order of the key values of the B-tree index corresponding to the primary key.

ASC:

Specify this if you want the key values of the B-tree index corresponding to the primary key to be sorted in ascending order.

DESC:

Specify this if you want the key values of the B-tree index corresponding to the primary key to be sorted in descending order.

If you specify DESC for a single-column index, it will be ignored. The key values of a single-column index are always sorted in ascending order (it is assumed that ASC is specified).

If neither ASC nor DESC is specified, the system assumes that ASC is specified.

IN DB-area-name [Common]

Specify the name of the DB area in which to store the B-tree index corresponding to the primary key.

If the IN DB-area-name specification is omitted, the B-tree index for the primary key is stored in the DB area specified for the adb_sql_default_dbarea_shared operand in the server definition.

Note that if the IN DB-area-name specification is omitted when either of the following conditions is met, the CREATE TABLE statement will result in an error:

  • Specification of the adb_sql_default_dbarea_shared operand is omitted in the server definition.

  • A non-existent DB area or a DB area other than a data DB area is specified for the adb_sql_default_dbarea_shared operand in the server definition.

PCTFREE=percentage-of-unused-area [Common]

~ <unsigned integer> ((0 to 99)) <<30>> (unit: %)

Specifies the percentage of unused area to maintain in the index pages of the B-tree index corresponding to the primary key. Specify a percentage from 0 to 99. If omitted, 30% is assumed.

When data is imported and the B-tree index for the primary key is created, the B-tree index data will be stored leaving the percentage of unused area specified here. The B-Tree index data will also be stored in this way when the B-tree index for the primary key is rebuilt.

For details about the percentage of unused area in an index page, see Allocating an unused area inside a B-tree index page (PCTFREE) or Allocating an unused area inside a text index page (PCTFREE) in the HADB Setup and Operation Guide.

(g) referential-constraint-definition [Common]

referential-constraint-definition ::= [CONSTRAINT constraint-name] FOREIGN KEY (column-name[,column-name]...)
                           REFERENCES table-name DISABLE

Specify this if you want to define a referential constraint (foreign key) for the base table. The foreign key can be defined as a column (or combination of multiple columns) that references the primary key of another table.

For more information about the benefits of defining a foreign key, see Specifying a foreign key (FOREIGN KEY) in the HADB Setup and Operation Guide.

Note

The columns that make up the foreign key and the columns that make up the primary key must be the same in all of the following respects:

  • The number of columns

  • The data type of each column

  • The data length of each column

Note the following rules:

  • A maximum of 255 foreign keys can be defined for one table.

  • A maximum of 255 foreign keys can be defined that reference one primary key.

  • You cannot define multiple referential constraints that reference the same primary key from the same foreign key. In this context, the same foreign key means a foreign key that satisfies the following condition:

    • The columns that make up the foreign key are the same

    A foreign key composed of multiple columns is considered the same foreign key even when the order of columns differs from the order in the definition.

CONSTRAINT constraint-name [Common]

Specifies a name for the referential constraint definition specified here. For rules on specifying a constraint name, see (2) Rules for characters that can be used in names in 6.1.4 Specifying names.

Note the following rules:

  • If the same constraint name (including uniqueness constraint names) exists in the same schema, executing the CREATE TABLE statement will result in an error.

  • If this specification is omitted, a name of the following form is generated as the constraint name:

    FOREIGN_nnnnnnnn_YYYYMMDDhhmmssth

    nnnnnnnn: The table ID of the table for which the foreign key is to be defined, converted to a character string of eight hexadecimal digits

    YYYYMMDDhhmmssth: The time stamp when the foreign key was defined (output to the hundredth of a second)

    If a constraint with the same name as the generated name exists in the same schema, executing the CREATE TABLE statement will result in an error. It is therefore recommended that you avoid the above format when specifying constraint names (including uniqueness constraint names) or index identifiers.

column-name [Common]

Specifies the names of the columns that are to make up the foreign key.

Note the following rules:

  • A maximum of 16 column names can be specified.

  • If multiple column names are specified, each must be unique.

table-name [Common]

Specifies the name of the referenced table (the base table where the primary key is defined).

Note the following rules:

  • The referenced table cannot be the table where the foreign key is defined.

DISABLE [Common]

This option (referential constraint check suppression) specifies to suppress checking of the foreign key referential constraint.

This option must be specified. If it is not specified, the CREATE TABLE statement will result in an error.

(h) IN DB-area-name [Common]

Specifies the name of the DB area where the table is to be stored.

If the IN DB-area-name specification is omitted, the table is stored in the DB area specified for the adb_sql_default_dbarea_shared operand in the server definition.

Note that if the IN DB-area-name specification is omitted when either of the following conditions is met, the CREATE TABLE statement will result in an error:

  • Specification of the adb_sql_default_dbarea_shared operand is omitted in the server definition.

  • A non-existent DB area or a DB area other than a data DB area is specified for the adb_sql_default_dbarea_shared operand in the server definition.

(i) PCTFREE=percentage-of-unused-area [Row store table]

~ <unsigned integer> ((0 to 99)) <<30>> (unit: %)

Specifies the percentage of unused area to maintain in a data page (the pages that store the data for the table). Specify a percentage from 0 to 99. If omitted, 30 (%) is assumed.

When data is imported, the data will be stored leaving the percentage of unused area specified here.

Note that when you add a row with an INSERT statement, or update a row with an UPDATE statement, the percentage of unused area specified here does not apply (the added or updated data is stored in the unused area reserved by this keyword).

For details about the percentage of unused area in a data page, see Allocating an unused area inside the data page (PCTFREE) [Row store table] in the HADB Setup and Operation Guide.

Note that PCTFREE cannot be specified more than once.

(j) BRANCH ALL [Row store table]

Branch all the VARCHAR-type and VARBINARY-type column data defined in the table.

For information about cases where BRANCH ALL is appropriate, see Branch specification for column data of variable-length data types (BRANCH) [Row store table] in the HADB Setup and Operation Guide.

Note that you cannot specify BRANCH ALL when FIX is specified.

(k) chunk-specification [Common]

chunk-specification ::= CHUNK[=maximum-number-of-chunks]
                       [chunk-archive-specification]
  chunk-archive-specification ::= ARCHIVABLE
                                RANGECOLUMN=column-name
                               [RANGEINDEXNAME=index-identifier]
                               [IN DB-area-name]
                                ARCHIVEDIR=archive-directory-name

Use the preceding specification when you define a base table as a multi-chunk table.

For details about designing a multi-chunk table, see Points to consider in defining a multi-chunk table in the HADB Setup and Operation Guide.

The following table shows the relationship between the types of multi-chunk tables and the functions that can be used.

Table 3‒9: Types of multi-chunk tables and the functions that can be used

Function name

Multi-chunk table type

Regular multi-chunk table

Archivable multi-chunk table

Background-import facility

Y

Y

Deleting all rows in a chunk by using the PURGE CHUNK statement

Y

Y

Chunk archiving function

N

Y

Legend:

Y: Can be used.

N: Cannot be used.

For details about the background-import facility and chunk archiving function, see the following sections in the HADB Setup and Operation Guide: Background-import facility and Chunk archiving function (compressing data in a chunk).

CHUNK[=maximum-number-of-chunks] [Common]

~ <unsigned integer> ((2 to 30,000)) <<256>> (unit: chunks)

Specify the maximum number of chunks in a multi-chunk table. If you specify only the keyword CHUNK without specifying a value (maximum-number-of-chunks), the system assumes that 256 is set as maximum-number-of-chunks.

If you define a regular multi-chunk table, specify only CHUNK=maximum-number-of-chunks.

If you define an archivable multi-chunk table, specify CHUNK=maximum-number-of-chunks and the chunk-archive specification described later.

chunk-archive-specification [Row store table]
chunk-archive-specification ::= ARCHIVABLE
                              RANGECOLUMN=column-name
                             [RANGEINDEXNAME=index-identifier]
                             [IN DB-area-name]
                              ARCHIVEDIR=archive-directory-name

Use the preceding specification when you define a base table as an archivable multi-chunk table.

RANGECOLUMN=column-name

Specify a column name. The column specified here becomes the archive range column.

You cannot use columns that have the following data types as archive range columns:

  • CHARACTER type (only if the defined length is 33 bytes or more)

  • VARCHAR type

  • BINARY type

  • VARBINARY type

Note that NOT NULL constraint is set for archive range columns.

RANGEINDEXNAME=index-identifier

When the CREATE TABLE statement is run, the HADB server automatically defines a range index whose columns include an archive range column. Specify the index identifier to be assigned to this range index.

If RANGEINDEXNAME is not specified, the HADB server determines the index identifier in the following format:

ARCHIVE_RANGE_INDEX_nnnnnnnn

nnnnnnnn is an eight-digit character string that represents the ID of the archivable multi-chunk table in hexadecimal notation.

If the automatically determined index identifier already exists in the same schema, the CREATE TABLE statement will result in an error. Therefore, if you use the CREATE INDEX statement to define an index, we recommend that you do not use a name whose format resembles the preceding format.

Note

The range index that is automatically defined here is subject to the same rules as a range index defined by the CREATE INDEX statement.

IN DB-area-name

Specify the name of the DB area in which to store the range indexes that are automatically defined.

If the IN DB-area-name specification is omitted, automatically defined range indexes are stored in the DB area specified for the adb_sql_default_dbarea_shared operand in the server definition.

Note that if the IN DB-area-name specification is omitted when either of the following conditions is met, the CREATE TABLE statement will result in an error:

  • Specification of the adb_sql_default_dbarea_shared operand is omitted in the server definition.

  • A non-existent DB area is specified for the adb_sql_default_dbarea_shared operand in the server definition.

ARCHIVEDIR=archive-directory-name

Specify the absolute path name of the archive directory in which to store archive files.

The following rules apply:

  • Specify the archive directory name in the character string literal format. For details about character string literals, see 6.3 Literals.

  • Specify an existent directory for the archive directory. Make sure that read, write, and execution permissions for the HADB administrator are set on the directory that you specify.

    Also, make sure that execution permission for the HADB administrator is set on all directories that are included in the path of the archive directory.

    (Example) If the archive directory is /HADB/archive:

    For the /HADB/archive directory, read, write, and execution permissions must be set.

    For the / directory and the /HADB directory, the execution permission is necessary.

  • The following directories cannot be used as the archive directory:

    • Server directory

    • Subdirectory of a server directory

    • Directory that contains a server directory

    • DB directory

    • Subdirectory of a DB directory

    • Directory that contains a DB directory

    • Root directory

    The following shows examples of directories that can be and cannot be used as the archive directory when the DB directory is /HADB/db:

    Directory

    Reason

    Example of directory that can be used as the archive directory

    /HADB/archive

    None.

    Example of directory that cannot be used as the archive directory

    /HADB/db

    This directory is the same as the DB directory.

    /HADB/db/archive

    This directory is a subdirectory of the DB directory.

    /HADB

    This directory contains the DB directory.

  • Do not specify (as the archive directory) a directory in which installation data was stored when the HADB server was installed.

  • The name of the archive directory must be 1 to 400 bytes long except the heading and trailing spaces.

    Note

    If you specify a directory name that begins and/or ends with spaces, these spaces are deleted (the resulting character string is used as the archive directory name).

  • Make sure that each element of the archive directory name is no more than NAME_MAX bytes long. The NAME_MAX value differs depending on the environment.

If a symbolic link is specified as the archive directory name, the system checks whether the absolute path name that the symbolic link substitutes for obeys the rules that are described here.

About the multi-node function:

If the multi-node function is enabled, note the following points:

  • Use the NFS or other means to share the archive directory by all nodes. Note that it must have been shared by all nodes when the CREATE TABLE statement is run.

  • On the master node, when the CREATE TABLE statement is run, a check to see whether the archive directory name obeys the specification rules that are described here is conducted. This check is not conducted on the slave nodes. Therefore, after the CREATE TABLE statement, check the archive directory name on each slave node.

About the location table that is defined when an archivable multi-chunk table is defined

If an archivable multi-chunk table is defined by running the CREATE TABLE statement, the HADB server automatically defines the location table and the index of the location table. The HADB server uses the location table and index. Therefore, no user can directly manipulate, redefine, or delete the location table or index. For details about the location table, see Searching an archivable multi-chunk table in the HADB Setup and Operation Guide.

The location table and its index are stored in the same DB area as the archivable multi-chunk table.

The names of the location table and its index are determined according to the rules that are described in the following table.

Table 3‒10: Naming rules for the location table and location table index

Item

Naming rule

Information managed by the index

Columns in the index

Location table

"HADB"."LOCATION_TABLE_nnnnnnnn"

--

--

Location table index

"HADB"."LOCATION_INDEX_nnnnnnnn_CHUNK_ID"

Manages the chunk ID of the chunk that corresponds to the archive file.

CHUNK_ID

"HADB"."LOCATION_INDEX_nnnnnnnn_RANGE_01"

Manages the range (upper and lower limits) of values in the archive range column of data stored in the archive file.

  • RANGE_MAX

  • RANGE_MIN

"HADB"."LOCATION_INDEX_nnnnnnnn_RANGE_02"

Manages the lower limit of values in the archive range column of data stored in the archive file.

RANGE_MIN

Legend:

--: Not applicable.

Note:

nnnnnnnn is an eight-digit character string that represents the ID of the archivable multi-chunk table in hexadecimal notation.

The schema name of the location table and location table index is HADB.

(l) STORAGE FORMAT {ROW|COLUMN} [Common]

Specifies the table-data storage format of a table to be defined.

ROW:

Specify this keyword when you define a table that has row store format as the table-data storage format. If ROW is specified, the table is defined as a row store table.

COLUMN:

Specify this keyword when you define a table that has column store format as the table-data storage format. If COLUMN is specified, the table is defined as a column store table.

If the STORAGE FORMAT specification is omitted, the system assumes that ROW is specified.

Note
  • For details about the row store table, row store format, column store table, and column store format, see Row store tables and column store tables in the HADB Setup and Operation Guide.

  • The specification of this option is called table-storage-format specification.

(3) Privileges required at execution

To execute the CREATE TABLE statement, the CONNECT privilege and schema definition privilege are required.

If you also want to specify a referential constraint (foreign key), the REFERENCES privilege on the referenced table is required.

(4) Rules

  1. A base table can only be defined in the schema owned by the current user (the HADB user whose authorization identifier is currently connected to the HADB server). You cannot define a base table in a schema owned by another HADB user.

  2. A maximum of 4,096 base tables can be defined in the system (excluding the base tables of dictionary tables and system tables).

  3. A maximum of 200 base tables can be stored in one DB area.

  4. A maximum of 1,000 columns can be defined in one table.

  5. Columns must be defined such that the sum of the sizes of all columns in the base table (the row length) satisfies the following inequality:

    • If the base table is a row store table:

      ROWSZ (row-length) ≤ page-size - 56
    • If the base table is a column store table:

      ROWSZ (row-length) ≤ page-size - 80

    For details about the formula for calculating ROWSZ (row length), see Determining the number of pages for storing each type of row in the HADB Setup and Operation Guide.

  6. If you use a chunk specification, you cannot define a primary key. Also, if you define a primary key, you cannot use a chunk specification.

  7. You cannot define a primary key on a column on which a B-tree index cannot be defined.

  8. You can define a primary key only when the following conditions are met. You cannot define a primary key unless the following conditions are met.

    • For a primary key consisting of one column

    Defined length of column in primary key#1 ≤ MIN{(a ÷ 3) - 128, 4,036} (unit: bytes)
    • For a primary key consisting of two or more columns

    Total defined length of columns in primary key#2 ≤ MIN{(a ÷ 3) - 128, 4,036} (unit: bytes)

    a: Page size of DB area where B-tree index corresponding to primary key is to be stored

    #1

    For details about the defined length of columns, see Table 3‒5: Size of a column that comprises a single-column index.

    #2

    For details about the defined length of each column, see Table 3‒6: Size of columns that comprise a multiple-column index. Then, obtain the total defined size of columns that comprise the primary key.

  9. The tables defined after a transaction is started cannot be accessed from the transaction.

(5) Examples

Examples 1 to 6 are examples of defining a row store table. Example 7 is an example of defining a column store table.

Example 1: Define a base table that is not a FIX table

Define a shops table (SHOPSLIST). Let the shops table's column structure, percentage of unused area, and so on, be as follows:

  • Shop code (SHOP_CODE): CHAR(8)

  • Region code (RGN_CODE): CHAR(6)

  • Shop name (SHOP_NAME): VARCHAR(20)

  • Shop telephone number (TEL_NO): CHAR(10)

  • Shop address (ADDRESS): VARCHAR(300)

  • Define the NOT NULL constraint for every column.

  • For storing, branch the data in the shop's address column (ADDRESS).

  • Store the shops table in DB area DBAREA01.

  • Let the percentage of unused area in a data page be 40%.

  • Let the maximum number of chunks be 100.

CREATE TABLE "SHOPSLIST"
        ("SHOP_CODE" CHAR(8) NOT NULL,
         "RGN_CODE" CHAR(6) NOT NULL,
         "SHOP_NAME" VARCHAR(20) NOT NULL,
         "TEL_NO" CHAR(10) NOT NULL,
         "ADDRESS" VARCHAR(300) NOT NULL BRANCH YES)
      IN "DBAREA01"
      PCTFREE=40
      CHUNK=100
Example 2: Define a FIX table

Define a sales history table (SALESLIST). Let the sales history table's column structure, percentage of unused area, and so on, be as follows:

  • Customer ID (USERID): CHAR(6)

  • Product code (PUR-CODE): CHAR(4)

  • Quantity purchased (PUR-NUM): SMALLINT

  • Date of purchase (PUR-DATE): DATE

  • Set a default column value for the date of purchase (PUR-DATE) column by specifying a DEFAULT clause.

  • Store the sales history table in the DB area DBAREA01.

  • Let the percentage of unused area in a data page be 20%.

  • Specify FIX because the row length is fixed.

  • Let the maximum number of chunks be 200.

CREATE FIX TABLE "SALESLIST"
        ("USERID" CHAR(6),
         "PUR-CODE" CHAR(4),
         "PUR-NUM" SMALLINT,
         "PUR-DATE" DATE DEFAULT CURRENT_DATE)
      IN "DBAREA01"
      PCTFREE=20
      CHUNK=200
Example 3: Define a base table with a primary key

Define a sales history table (SALESLIST). Let the sales history table's column structure, percentage of unused area, and so on, be as follows:

  • Customer ID (USERID): CHAR(6)

  • Product code (PUR-CODE): CHAR(4)

  • Quantity purchased (PUR-NUM): SMALLINT

  • Date of purchase (PUR-DATE): DATE

  • Store the sales history table in the DB area DBAREA01.

  • Let the percentage of unused area in a data page be 20%.

  • Specify FIX because the row length is fixed.

  • Define a primary key (let the customer ID column (USERID) be the column that comprises the primary key).

  • Store the B-tree index corresponding to the primary key in the DB area DBAREA02.

  • Set the percentage of unused area in the index page of the B-tree index corresponding to the primary key to 20%.

CREATE FIX TABLE "SALESLIST"
      ("USERID" CHAR(6),
       "PUR-CODE" CHAR(4),
       "PUR-NUM" SMALLINT,
       "PUR-DATE" DATE,
       CONSTRAINT "PK-USERID" PRIMARY KEY ("USERID" ASC)
            IN "DBAREA02" PCTFREE=20)
    IN "DBAREA01"
    PCTFREE=20

The underlined portion indicates the primary key definition (uniqueness constraint definition).

Example 4: Define a base table with a primary key

Define a shops table (SHOPSLIST). Let the shops table's column structure, percentage of unused area, and so on, be as follows:

  • Shop code (SHOP_CODE): CHAR(8)

  • Region code (RGN_CODE): CHAR(6)

  • Shop name (SHOP_NAME): VARCHAR(20)

  • Shop telephone number (TEL_NO): CHAR(10)

  • Shop address (ADDRESS): VARCHAR(300)

  • For storing, branch the data in the shop's address column (ADDRESS).

  • Store the shops table in DB area DBAREA01.

  • Let the percentage of unused area in a data page be 40%.

  • Define a primary key (let the shop code column (SHOP_CODE) and the region code column (RGN_CODE) be the columns that comprise the primary key).

  • Store the B-tree index corresponding to the primary key in the DB area DBAREA02.

  • Set the percentage of unused area in the index page of the B-tree index corresponding to the primary key to 20%.

CREATE TABLE "SHOPSLIST"
      ("SHOP_CODE" CHAR(8),
       "RGN_CODE" CHAR(6),
       "SHOP_NAME" VARCHAR(20),
       "TEL_NO" CHAR(10),
       "ADDRESS" VARCHAR(300) BRANCH YES,
       CONSTRAINT "PK-CODE" PRIMARY KEY ("SHOP_CODE" ASC,"RGN_CODE" ASC)
            IN "DBAREA02" PCTFREE=20)
    IN "DBAREA01"
    PCTFREE=40

The underlined portion indicates the primary key definition (uniqueness constraint definition).

Example 5: Define a base table with a foreign key

Define a shops table (SHOPSLIST) and an employee table (EMPLOYEE). Define the primary key and foreign key as follows:

  • Define the primary key on the shops table (SHOPSLIST). The primary key will consist of the SHOP_CODE column and the RGN_CODE column from the shops table (SHOPSLIST).

  • Define the foreign key on the employee table (EMPLOYEE). The foreign key will consist of the SHOP_CODE column and the RGN_CODE column from the employee table (EMPLOYEE).

Shops table (SHOPSLIST)

CREATE TABLE "SHOPSLIST"
      ("SHOP_CODE" CHAR(8),
       "RGN_CODE" CHAR(6),
       "SHOP_NAME" VARCHAR(20),
       "TEL_NO"    CHAR(10),
       "ADDRESS"   VARCHAR(300) BRANCH YES,
       CONSTRAINT "PK-CODE" PRIMARY KEY ("SHOP_CODE" ASC,"RGN_CODE" ASC)
              IN "DBAREA02" PCTFREE=20)
    IN "DBAREA01"
    PCTFREE=40

The underlined portion indicates the primary key definition.

Employee table (EMPLOYEE)

CREATE TABLE "EMPLOYEE"
      ("EMPLOYEE_CODE"    CHAR(8),
       "FIRST_NAME"       VARCHAR(8),
       "FIRST_NAME_YOMI"  VARCHAR(16),
       "FAMILY_NAME"      VARCHAR(8),
       "FAMILY_NAME_YOMI" VARCHAR(16),
       "SHOP_CODE"        CHAR(8),
       "RGN_CODE"        CHAR(6),
       "EMPLOYEE_TYPE"    CHAR(1),
       "TEL_NO"           CHAR(10),
       "ADDRESS"          VARCHAR(300) BRANCH YES,
       CONSTRAINT "PK-EMPLOYEE_CODE" PRIMARY KEY ("EMPLOYEE_CODE" ASC)
             IN "DBAREA02" PCTFREE=20,
       CONSTRAINT "FK-SHOP_CODE" FOREIGN KEY ("SHOP_CODE","RGN_CODE")
             REFERENCES "SHOPSLIST" DISABLE)
    IN "DBAREA01"
    PCTFREE=40

The underlined portion indicates the foreign key (referential constraint) definition.

Example 6: Define an archivable multi-chunk table

Define a receipt table (RECEIPT) as an archivable multi-chunk table. Specify the chunk-related settings under the following conditions:

  • Let the maximum number of chunks be 120.

  • The RECORD_DAY column is used as the archive range column.

  • The /mnt/nfs/archivedir directory is used as the archive directory.

CREATE TABLE "RECEIPT"
      ("RID" INTEGER,
       "SHOP_CODE"      CHAR(8),
       "RGN_CODE"       CHAR(6),
       "EMPLOYEE_CODE"  CHAR(8),
       "CUSTOMER_CODE"  CHAR(8),
       "RECORD_DAY"     DATE,
       "ITEM_CODE"      CHAR(8),
       "ITEM_PRICE"     INTEGER)
    IN "DBAREA01"
    PCTFREE=30
    CHUNK=120
      ARCHIVABLE RANGECOLUMN="RECORD_DAY" IN "DBAREA02"
                 ARCHIVEDIR='/mnt/nfs/archivedir'

In the preceding example, the underlined parts are the settings for defining an archivable multi-chunk table.

Example 7: Define a column store table

Define a receipt table (RECEIPT) as a column store table.

CREATE TABLE "RECEIPT"
      ("RID"            INTEGER,
       "SHOP_CODE"      CHAR(8),
       "RGN_CODE"       CHAR(6),
       "EMPLOYEE_CODE"  CHAR(8),
       "CUSTOMER_CODE"  CHAR(8),
       "RECORD_DAY"     DATE,
       "ITEM_CODE"      CHAR(8),
       "ITEM_PRICE"     INTEGER)
    IN "DBAREA01"
    CHUNK=120
    STORAGE FORMAT COLUMN

In the preceding example, the underlined portion indicates a specification specific to column store tables.