3.7.1 Specification format and rules for the CREATE TABLE statement

(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.