CREATE TABLE (Define table)

Function

CREATE TABLE defines a table.

Privileges

Owner of a schema

The owner of a schema can define tables in either private user RDAREAs or public user RDAREAs for which the owner has the RDAREA usage privilege.

Format

The numbers in the left column below correspond to the section numbers on the following pages where the operands are explained.

No.Format
1

CREATE [[SHARE] FIX] TABLE

2

    [authorization-identifier.]table-identifier

3

    (table-element [, table-element]...)

4

    [{IN {table-storage-RDAREA-name
          |(table-storage-RDAREA-name)
          |([(table-storage-RDAREA-name) storage-condition,]...
            (table-storage-RDAREA-name) [storage-condition])}
      |PARTITIONED BY column-name
         IN([(table-storage-RDAREA-name) boundary-value,]...
             (table-storage-RDAREA-name) boundary-value,
               (table-storage-RDAREA-name))
      |PARTITIONED BY MULTIDIM
          (first-dimension-column-name first-dimension-boundary-value-list
           , {second-dimension-column-name second-dimension-boundary-value-list
         | [FIX] HASH [hash-function-name]
           BY second-dimension-column-name [,second-dimension-column-name]...})
          IN matrix-partitioned-table-storage-RDAREA-specification
      |[FIX] HASH [hash-function-name] BY column-name
                [, column-name]...
        IN(table-storage-RDAREA-name, table-storage-RDAREA-
                name,...)}]

5

    [table-option]...

6

    [table-restriction-definition]...

27

 [WITH PROGRAM]

Legend:
[Figure]: See applicable items.

1 For a column in a FIX table or a column that is a member of a cluster key or primary key

2 For a column other than the above

3 The position of a restriction definition is determined by the specification value in the system common definition, pd_constraint_name operand, or that in the client environment variable PDCNSTRNTNAME. For details, see 6) Table-restriction-definition.

Operands

1) [SHARE]FIX

Specifies that the table is to have a fixed row length. To store table data in a shared RDAREA and make it a shared table, specify SHARE.

However, on a HiRDB/Single Server that has no shared RDAREAs, a shared table can be defined by specifying SHARE to maintain SQL compatibility with HiRDB/Parallel Server. In this case, table data is stored in the regular user RDAREA.

When the FIX option is specified, database operations that change the row length cannot be performed; however, the efficiency of row storage is enhanced. When used in conjunction with the row-unit interface, the FIX option can improve access efficiency for a table that contains a large number of columns.

The following rules apply to the FIX option:

  1. The FIX option is incompatible with the following data types:
    • VARCHAR
    • NVARCHAR
    • MVARCHAR
    • BLOB
    • Abstract data type
  2. A repetition column for which FIX has been specified cannot be specified.
  3. The FIX option can be specified only if the row length does not exceed the following value:
    [Figure]Page length of RDAREA in which rows are stored |1000[Figure][Figure]1000
  4. The NOT NULL option is assumed for all columns of a table for which the FIX option is defined.
  5. If both SHARE and FIX are specified, table data cannot be saved on a split basis in multiple RDAREAs.
2) [authorization-identifier.] table-identifier
authorization-identifier
Specifies the authorization identifier of the owner of the base table being defined.
table-identifier
Specifies a name for the base table being defined. Each table identifier must be unique among the tables of the specified owner.
3) table-element::={column-definition ||table-restriction-definition}
column-definition
Defines a column (column name, data type, etc.) that is to compose the table. The NOT NULL constraint, uniqueness constraint, check constraint, and referential constraint items can be specified for each column.
4) {IN {table-storage-RDAREA-name
|(table-storage-RDAREA-name)
|([(table-storage-RDAREA-name) storage-condition,] ...
(table-storage-RDAREA-name) [storage-condition])}
|PARTITIONED BY column-name
IN ([(table-storage-RDAREA-name) boundary-value,] ...
(table-storage-RDAREA-name) boundary-value,
(table-storage-RDAREA-name))
|PARTITIONED BY MULTIDIM
(first-dimension-column-name first-dimension-boundary-value-list
{second-dimension-column-name second-dimension-boundary-value-list
| [FIX] HASH [hash-function-name]
BY second-dimension-column-name [, second-dimension-column-name]...})
IN matrix-partitioned-table-storage-RDAREA-specification
|[FIX] HASH [hash-function-name] BY column-name [, column-name] ...
IN (table-storage-RDAREA-name, table-storage-RDAREA-name,...)}
IN
Specifies the RDAREAs in which table rows are to be stored.
table-storage-RDAREA-name
Specifies the name of a user RDAREA in which rows of the table are to be stored.
However, HiRDB/Parallel Server is subject to the following restrictions. If SHARE is specified, the name of a shared RDAREA must be specified. Conversely, if SHARE is not specified, the name of a shared RDAREA cannot be specified.
If an RDAREA name is omitted, the RDAREA for storing the table is determined as described below. In addition, if SHARE is specified on HiRDB/Parallel Server, a shared RDAREA is identified as a candidate storage area. Conversely, if SHARE is not specified, a shared RDAREA is not identified as a candidate storage area.
A user RDAREA that stores rebalancing tables cannot be specified.

Table 3-25 How a table storage RDAREA is determined by default

Primary key or cluster key specifiedIndex storage RDAREA specified*Table storage RDAREA determination method
No[Figure]A table storage RDAREA is determined according to the following priority:
  1. Of the public RDAREAs to which the inner replica facility is not applied, the public RDAREA that has the fewest defined tables
  2. If there is more than one RDAREA meeting condition 1, the first RDAREA found by HiRDB
  3. If a determination cannot be made on the basis of conditions 1 and 2, of the RDAREAs (the original public RDAREAs subject to the inner replica facility), the public RDAREA having the fewest defined tables
  4. If there is more than one RDAREA meeting condition 3, the first RDAREA found by HiRDB
YesNoA table storage RDAREA is determined according to the following priority:
  1. Of the public RDAREAs to which the inner replica facility is not applied, the public RDAREA that has the fewest defined tables
  2. If there is more than one RDAREA meeting condition 1, the public RDAREA having the fewest index definitions
  3. If there is more than one RDAREA meeting conditions 1 and 2, the first RDAREA found by HiRDB
  4. If a determination cannot be made on the basis of conditions 1, 2, and 3, of the RDAREAs (the original public RDAREAs subject to the inner replica facility), the public RDAREA having the fewest defined tables
  5. If there is more than one RDAREA meeting condition 4, the public RDAREA having the fewest index definitions
  6. If there is more than one RDAREA meeting conditions 4 and 5, the first RDAREA found by HiRDB
RDAREA not subject to inner replica facility
  1. Of the public RDAREAs to which the inner replica facility is not applied, the public RDAREA that has the fewest defined tables
  2. If there is more than one RDAREA meeting condition 1, the first RDAREA found by HiRDB
RDAREA (original RDAREA) subject to inner replica facility
  1. Of the RDAREAs subject to the inner replica facility, the original and public RDAREAs having equal numbers of replica RDAREAs
  2. If there is more than one RDAREA meeting condition 1, the first RDAREA found by HiRDB
Legend:
[Figure]: Not applicable
* On HiRDB/Parallel Server, if an RDAREA for index is specified, the RDAREA residing on the same server as the RDAREA for index is subject to table-storage RDAREA selection.
storage-condition
Specifies conditions for storing the table in multiple RDAREAs on a split basis (row-partitioning of the table). If a storage condition is specified, single-column partitioning will be used in partitioning the table.
Multiple literals can be specified as the storage condition for a column such as the following:
  • Column whose values cannot be grouped for specification of ranges (e.g., store numbers, organization codes)
  • Column consisting of noncontiguous values, such as character strings
The following rules apply to storage-condition:
  1. When multiple storage conditions are specified, the same column name must be specified in all of them.
  2. When multiple storage conditions are specified, HiRDB evaluates them in the order in which they are specified; rows are stored in the RDAREA associated with the first storage condition that tests TRUE. Rows for which no condition is TRUE are stored in an RDAREA for which no storage conditions are specified. If there is no RDAREA for which no storage conditions are specified, rows cannot be stored.
  3. If there is an RDAREA in which no rows are stored as a result of HiRDB's evaluation of storage conditions, the table is not defined.
  4. For data insertion, data cannot be inserted if there is no RDAREA corresponding to the data.
  5. For data updating, you cannot update column values that are specified in storage conditions.
  6. Each storage condition is associated with one RDAREA. A maximum of 1,024 RDAREAs can be specified. The same RDAREA cannot be specified for more than one storage condition.
PARTITIONED BY column-name
Specifies that the table is to be partitioned by boundary values for storage in multiple RDAREAs. The maximum number of RDAREAs into which a table can be partitioned is 1,024, exclusive of duplications.
column-name
Specifies the name of the column for which boundary values are to be specified. The column data types that can be specified are the data types on which storage condition comparison operations can be performed. When PARTITIONED BY column-name is specified, the resulting partitioning will be single-column partitioning.
The following rules apply to BY column-name:
  1. Values in the column that is specified in column-name cannot be updated.
  2. The column specified in column-name should be a NOT NULL column (NOT NULL constrained, FIX specification, cluster key, or primary key).
  3. If a cluster key is specified for any column, boundary values cannot be specified for any other columns.
  4. If multiple columns constitute a cluster key, boundary values cannot be specified for any of those columns except the first column.
  5. A repetition column cannot be specified.
boundary-value
Specifies a boundary value for determining where the table's rows are to be partitioned. Specify a literal as a boundary-value.
The following rules apply to boundary values:
  1. Any of the following items cannot be specified in literal:
    [Figure]Character string literals, national character string literals, or mixed character string literals with a length of 0
    [Figure]Character string literals with a length of 256 bytes or greater, national character string literals with a length of 128 characters or greater, or mixed or character string literals with a length of 256 bytes or greater
    [Figure]Hexadecimal character string literals
  2. Boundary values must be specified in ascending order; they must all be distinct values.
  3. A maximum value should not be specified in the boundary value that is specified last.
  4. Specify table storage RDAREAs and boundary values alternately so that the specification begins and ends with a table storage RDAREA.
  5. The maximum number of table storage RDAREAs is 3,000.
  6. The same table storage RDAREA can be specified multiple times, provided that it is not specified twice in succession.
  7. The first RDAREA for which a boundary value is specified will store the rows whose value is less than or equal to the specified boundary value. In each subsequent RDAREA (except for the last one), rows are stored that have a value greater than the previously specified boundary value and less than or equal to the next boundary value that is specified. The last RDAREA that is specified stores rows with a value that is greater than any of the previously specified boundary values.
PARTITIONED BY MULTIDIM
Specify this operand when partitioning table data into a column (first dimension partitioning column) and partitioning boundary value data into another column (second dimension partitioning column). Partitioning by this type of specification is called matrix partitioning, and tables that are partitioned in this manner are referred to as matrix-partitioned tables.
Defining a matrix-partitioned table requires HiRDB Advanced Partitioning Option.
first-dimension-column-name::=column-name
Specifies a first-dimension partitioned column name.
The following rules apply to first-dimension partitioned column names:
  1. The specified column should be made NOT NULL. The following methods can be used to make a column NOT NULL:
    [Figure]Defining the column as a FIX table
    [Figure]Specifying NOT NULL in the column definition
    [Figure]Defining a cluster key or the primary key
  2. The values in the specified column cannot be updated.
  3. A repetition column cannot be specified in column-name.
  4. For the data type of the column specified in column-name, see the data types of columns that can be compared under storage conditions.
first-dimension-boundary-value-list::=boundary-value-list
boundary-value-list::=((boundary-value)[, (boundary-value)]...)
Specifies the column boundary value list that was specified in first-dimension-column-name.
In boundary-value, specify the boundary value to be used for partitioning the rows in a table. The following rules apply to boundary values:
  1. Specify a literal in boundary-value.
  2. Specify boundary values in ascending order.
  3. Any of the following items cannot be specified in boundary-value:
    [Figure]Character string literals, national character string literals, or mixed character string literals with a length of 0
    [Figure]Character string literals and mixed or character string literals with a length of 256 bytes or greater
    [Figure]National character string literals with a length of 128 characters or greater
    [Figure]Hexadecimal character string literals
    The maximum value cannot be specified in the boundary value that is specified last.
second-dimension-column-name::=column-name
Specifies a second-dimension partitioned column name.
For details on rules, see the explanation of the first-dimension column name.
second-dimension-boundary-value-list::=boundary-value-list
boundary-value-list::=((boundary-value)[, (boundary-value)]...)
Specify a list of column boundary values that were specified in second-dimension-column-name.
For rules on boundary values, see the explanation of boundary values for the first-dimension boundary-value list.
[FIX] HASH [hash-function-name]
BY second-dimension-column-name [, second-dimension-column-name]...
second-dimension-column-name::= column-name
Specifies the names of the hash function and second-dimension column to be used.
For details about specification methods and rules, see the [FIX] HASH item.
matrix-partitioned-table-storage-RDAREA-specification::=two-dimensional-storage-RDAREA-specification
two-dimensional-storage-RDAREA-specification::=
(matrix-partitioning-RDAREA-list
[, matrix-partitioning-RDAREA-list]...)
matrix-partitoning-RDAREA-list ::=
(RDAREA-name[, RDAREA-name]...)
When defining a matrix-partitioned table, a cluster key, the primary key, a BLOB column, or an abstract data type of the BLOB attribute for a matrix-partitioned table, specifies the RDAREA that stores them.
The following rules apply to the RDAREA for the storage of matrix-partitioned tables:
  1. For rules on RDAREA names, see the explanation of RDAREAs under each operand.
  2. The number of RDAREAs specified per matrix-partitioned RDAREA list is the number of boundary values specified in second-dimension boundary value list + 1. If a hash function is used in second-dimension-partitioning-column, the number of RDAREAs is a user-specified number.
  3. The specifiable number of matrix-partitioned RDAREA lists is the number of boundary values specified in first-dimension boundary value list + 1.
  4. When defining a cluster key, the primary key, a BLOB column, and an abstract data type having the BLOB attribute for a matrix-partitioned table, specify RDAREAs in correspondence with the RDAREAs for storing matrix-partitioned tables.
  5. The maximum number of RDAREAs that can be specified, exclusive of duplicates, is 1,024.
  6. The total number of specifiable RDAREAs is 3,000.
  7. Although the same RDAREA name can be specified successively, the same RDAREA name cannot be specified for all RDAREAs. If a hash function is used in second-dimension-partitioning-column, table storage RDAREAs in the matrix-partitioning RDAREA list should be specified without any duplication.
  8. Cluster keys cannot be specified in a single column.
  9. If a cluster key extends over two or more columns, the columns specified for partitioning must be included in the same order from the beginning.
[FIX] HASH
Specifies that the table is to be partitioned by means of a hash function for storage in multiple RDAREAs. The same RDAREA name cannot be specified more than once. This operand is also specified when using a hash function in second-dimension-partitioning-column.
In this case, table storage RDAREAs in the matrix-partitioning RDAREA list should be specified without any duplication.
When a table is to be partitioned by flexible hash partitioning, specify HASH only; for FIX hash partitioning, specify FIX HASH.
[hash-function-name]
Specifies the hash function to be used for hash partitioning of the table.
If a hash function name is omitted, the following hash function is assumed, depending on the partitioning method involved:
  • If the table is partitioned by using a hash function, HASH1 is assumed.
  • If a hash function is specified in a second-dimension partitioning column in the matrix-partitioning table, HASH6 is assumed.
BY column-name[, column-name] ...
Specifies the names of the columns to be operated on by the hash function. The column data types that can be specified are data types that are eligible for storage condition comparison operations.
Specifying one column name only results in single-column partitioning; specifying multiple columns results in multicolumn partitioning.
For a single-column partitioned table:
  • If there is a column for which a cluster key is specified, no other columns can be specified.
  • If the cluster key includes more than one column, no columns other than the first cluster key column can be specified.
For a multicolumn partitioned table:
  • A cluster key cannot be specified for a single column.
  • The cluster key columns must include all columns that are specified for partitioning, beginning with the first column and in the same sequence.
Partitioning a second-dimension partitioning column in a matrix-partitioning table into single columns:
  • A cluster key cannot be specified for a single column.
  • If a cluster key is multiple columns, a column other than the second column from the beginning cannot be specified as a column name.
Partitioning a second-dimension partitioning column in a matrix-partitioning table into multiple columns:
  • A cluster key cannot be specified for a single column.
  • If a cluster key is multiple columns, the second and subsequent columns from the beginning must be specified in the same sequence (the second and subsequent columns from the beginning of the cluster key constituent columns need not all be included).
If there is a column for which the primary key is specified, whether or not the primary key can be defined depends on how the table is partitioned. For details about primary key definability (UNIQUE specifiability) see Table 3-20 Specifiability of UNIQUE in conjunction with row-partitioning of a table.
The following rules apply to BY column-name:
  1. A maximum of 16 columns can be specified for multicolumn partitioning. If a hash function is specified in second-dimension partitioning columns of a matrix-partitioned table, the maximum number of columns that can be specified is 15.
  2. The same column name cannot be specified more than once for multicolumn partitioning.
  3. For multicolumn partitioning, specify a combination of columns that have values that are not mutually dependent.
  4. A column specified in column-name should be a NOT NULL column (NOT NULL constrained, FIX specification, cluster key, or primary key).
  5. If flexible hash partitioning is specified for a flexible hash partitioned table or for second-dimension partitioning columns of a matrix-partitioned table, a cluster key with the UNIQUE specification, the primary key, or an index with the UNIQUE specification cannot be specified.
  6. For FIX hash-partitioning, the values in a column specified in column-name cannot be updated during data updating.
  7. A repetition column cannot be specified.
  8. If a column name is specified in a second-dimension partitioning column of a matrix-partitioned table, the column name specified in the first-dimension column name cannot be specified.
5) table-option ::=
{PCTFREE = {percentage-of-free-area
|([percentage-of-free-area],percentage-of-free-pages-in-segment)}
|{LOCK ROW|LOCK PAGE}
|SUPPRESS [DEC [IMAL]]
|WITHOUT ROLLBACK
|INDEX LOCK {NONE|PAGE}
|SEGMENT REUSE {[number-of-segments[{K|M|G}]]|NO}
|INSERT ONLY [WHILE {date-interval-data|labeled-duration} BY column-name]}
{PCTFREE = {percentage-of-free-area
|([percentage-of-free-area], percentage-of-free-pages-in-segment)}
The same option cannot be specified more than once for the same table.
The specification of parentheses is critical in the meaning of the PCTFREE specification:
PCTFREE = 30 specifies that 30% of the area is to be left unused (free area).
PCTFREE = (,30) specifies that 30% of the pages in each segment are to be left unused (free pages).
percentage-of-free-area
Specifies, in the range 0 to 99, a percentage of free area to be allocated in the database when the table is initialized. The default is 30 (%).
The percentage of unused space is applied when the database load utility or the database reorganization utility is executed. During other addition or update operations, such as the execution of the INSERT or UPDATE statements, the value PCTFREE = (0,0) is assumed.
The following rules apply to specifying percentage-of-free-area:
  1. When a cluster key is defined, PCTFREE should be specified for the following purposes:
    [Figure]To create free space in the table so that data to be inserted after data initialization or reorganization can be clustered.
    [Figure]If the table contains variable-length data, to create free space in the table so that data that is updated after data initialization or reorganization and that results in increasing a row length can be stored as close together as possible.
  2. PCTFREE = (0,0) should be specified for a fixed-length table that does not have a cluster key.
  3. When PCTFREE is specified for a fixed-length table that does not have a cluster key and updating increases the record length within a page, the specified free area in the page will be used.
  4. A high free area percentage should be specified for a table containing variable-length data and for which a cluster key is defined if rows will be added frequently after the table has been created or if frequent updating will result in increased row lengths.
percentage-of-free-pages-in-segment
Specifies, in the range 0 to 50, a percentage of free pages to be allocated in each segment when the table is created. The default is 10 (%).

The following formula can be used to calculate the percentage of free pages in a segment that should be specified:

[Figure]

The specified percentage of free pages in a segment is applied when the database load utility or the database reorganization utility is executed.

If the addition of rows occurs frequently in a table for which a cluster key is defined, or updates that increase the row length occur frequently, and if the data from the increase cannot fit in the unused area on the page, this value should be specified.

{LOCK ROW |LOCK PAGE}
Specifies the minimum unit of locked resources for retrieval and updating operations. Specification of LOCK ROW means that the row is the minimum locked resources unit; specification of LOCK PAGE means that the page is the minimum locked resources unit. The default is LOCK ROW.
LOCK ROW
Specifies that the row is the minimum unit for resources locking.
LOCK PAGE
Specifies that the page is the minimum unit for resources locking.
SUPPRESS [DEC[IMAL]]
For a non-FIX table, specifies that when data is stored in the table, part of the data can be omitted. The SUPPRESS option is useful for reducing the database's storage space requirements when the number of effective digits in the data to be stored in a column of the table is less than the column's defined length. For decimal-type data, the effective digits excludes leading zeros.
DEC[IMAL]
Specifies that when decimal-type data is stored in the table, leading zeros are to be omitted.
The following rules apply to the DECIMAL option:
  1. If SUPPRESS is specified and DECIMAL is omitted, DECIMAL is assumed.
  2. When the number of effective digits in decimal-type data is equal to the definition length, the data is stored with a length equal to definition length + 1. Note that this increases the data length compared to the case where the SUPPRESS option is specified.
  3. Note also that if the defined precision (total number of digits) of decimal-type data is 1, the length of the stored data is greater than when the SUPPRESS option is omitted.
  4. The DECIMAL attribute for the abstract data type is not subject to this function.
WITHOUT ROLLBACK
Specifies that the table is to be defined such that whenever updating of the table (including additions and deletions) is completed, the locking of the rows in the table is to be released without waiting for a commit of the updating transaction.
The following rules apply to the WITHOUT ROLLBACK option:
  1. The following table shows the applicability of row-locking and rollback during the updating (including additions and deletions) of rows in a given table.

    Table 3-26 Applicability of row-locking and rollback during the updating (including additions and deletions) of rows with a WITHOUT ROLLBACK specification

    Object of operationOperation on the table
    Row insertionUpdating a column in a rowRow deletion
    Updated value same as pre-update valueUpdated value different from pre-update value
    WITHOUT ROLLBACK-specified table with an index definitionThe column to be updated is an index constituent column.Row-locking is released when the transaction terminates. Even after processing, the transaction can be rolled back if it is not finished.Row-locking is released upon completion of the update. After processing, the update cannot be rolled back.Cannot be executed.Row-locking is released when the transaction terminates. Even after processing, the transaction can be rolled back if it is not finished.
    The column to be updated is not an index constituent column.Row-locking is released when the transaction terminates. After processing, the update cannot be rolled back.
    WITHOUT ROLLBACK-specified table without an index definitionRow-locking is released upon completion of the update. After processing, the update cannot be rolled back.
  2. This option is ignored during execution of the database load utility and the database reorganization utility.
  3. When this option is specified, the FIX option should also be specified for the table. This option is not specifiable if SHARE is specified.
  4. This option is not compatible with a BLOB column definition or with a LOCK PAGE specification.
INDEXLOCK {NONE| PAGE}
This option is provided for compatibility with XDM/RD; it is ignored if specified.
Index key value no-lock is specified in the pd_indexlock_mode operand of the system definition; for details of the pd_indexlock_mode operand, see the manual HiRDB Version 8 System Definition.
SEGMENT REUSE {[number-of-segments[{K|M|G}]]|NO}
This operand is specified when using the free space reusage facility on the table being defined. For details about the free space reusage facility, see the HiRDB Version 8 Installation and Design Guide.
number-of-segments[{K|M|G}]
When using the free space reusage facility and setting an upper limit on the number of segments for the table, specify the limit segment count. The number-of-segments operand is specified in the 1 to 268,435,440 range as an unsigned integer. Units K (kilo), M (mega), or G (giga) can be specified.
The use of this operand can improve row insertion efficiency for tables that are subject to frequent row insertions or deletions, and the storage efficiency in the specified segments.
number-of-segments not specified
The operand number-of-segments can be omitted when using the free space reusage facility on a table and an upper limit is not set on the number of segments in the table.
Use this operand when a table is subject to frequent row insertions or deletions and only the specified table is to be stored in the RDAREA. This operand also improves row insertion performance and the storage efficiency of free space in the RDAREA in which the specified table is to be stored.
NO
This operand is specified when not using the free space reusage facility.
Specify NO for tables that are not subject to frequent row insertions or deletions.
The following rules apply to SEGMENT REUSE:
  1. The free space reusage facility has no effect on LOB columns, abstract data type columns of the LOB attribute, or indexes.
  2. The free space reusage facility cannot be specified for rebalancing tables.
INSERT ONLY [WHILE {date-interval-data|labeled-duration} BY column-name]
This option is specified when making a given table into a falsification-prevented table. For details about falsification-prevented tables, see the HiRDB Version 8 Installation and Design Guide.
If a table is made into a falsification-prevented table, its values cannot be updated. However, its updatable columns can be updated.
Not all columns in a falsification-prevented table can be made into updatable columns.
For a falsification-prevented table, you can specify a period in which any deletion of rows is prohibited (a deletion-prevented duration). When specifying a deletion-prevented duration, specify a period in WHILE, and in column-name, specify an insert history maintenance column (a DATE-type column that is a SYSTEM GENERATED column). If a deletion-prevented duration is not specified for a given falsification-prevented table rows can no longer be deleted from the table, permanently.
date-interval-data
Specifies a deletion-prevented duration in the decimal representation of date interval data. For details about decimal representation of date interval data, see 1.3.4 Decimal representation of date interval data.
Note that date interval data can be specified in positive values only.
labeled-duration
In labeled-duration, specifies a deletion-prevented duration. For details about labeled durations, see 2.11 Date operations.
Only positive integer literals, not enclosed in parentheses, can be specified in the value expression of labeled-duration.
column-name
Specifies a DATE-type column that is SYSTEM GENERATED.
The deletion-prevented duration should include the date when a row was inserted. The deletion-prevented duration should be calculated according to the Rules for addition and subtraction of date data and date interval data in 2.11 Date operations. The last day of deletion prevention and the deletion-allowed date can be calculated as follows:
  • Last day of deletion prevention = row insertion date + deletion-prevented duration - 1
  • Deletion-allowed data = row insertion date + deletion-prevented duration
Table 3-27 shows the relationship between the last day of deletion prevention and the deletion-allowed data in terms of specified values of row insertion date and deletion-prevented duration.

Table 3-27 Relationship between last day of deletion prevention and the deletion-allowed data

Date of row insertionSpecified value for deletion-prevented durationLast day of deletion preventionDeletion-allowed date
2002-03-011 year12003-02-282003-03-01
1995-03-011 year11996-02-291996-03-01
2002-02-281 month22002-03-272002-03-28
2002-05-011 day32002-05-012002-05-02
1 Actual specification format: 00010000. for date interval data, 1 YEAR for labeled duration
2 Actual specification format: 00000100. for date interval data, 1 MONTH for labeled duration
3 Actual specification format: 00000001. for date interval data, 1 DAY for labeled duration
6) table-restriction-definition ::= {multicolumn-uniqueness-constraint-definition
[index-option [index-option]]
| {multicolumn-referential-constraint-definition [constraint-name-definition]
| [constraint-name-definition] multicolumn-referential-constraint-definition }
| {multicolumn-referential-constraint-definition [constraint-name-definition]
| [constraint-name-definition] multicolumn-referential-constraint-definition}}

This operand specifies a uniqueness constraint, check constraint, and referential constraint for multiple columns.

The position in which constraint-name-definition is specified is determined by the specification value of the system common definition pd_constraint_name operand or the specification value of the client environment variable PDCNSTRNTNAME. The following table shows allowable specification positions for the constraint name definition.

Table 3-28 Specification positions for the constraint name definition

Client environment variableSystem common definition
pd_constraint_name
Not specifiedLEADINGTRAILING
PDCNSTRNTNAMENot specifiedBeforeBeforeAfter
LEADINGBeforeBeforeBefore
TRAILINGAfterAfterAfter
Legend:
Before: A constraint name definition is specified before a constraint definition (standard SQL specifications).
After: A constraint name definition is specified after a constraint definition (XDM/RD-compatible specifications).

A multicolumn referential constraint cannot be specified if SHARE and FIX are specified.

7) column-definition::=
column-name data-type [ARRAY [maximum-number-of-elements]]
[NO SPLIT]
[{column-data-suppression-specification|[column-recovery-restriction]
{IN {LOB-column-storage-RDAREA-name
|(LOB-column-storage-RDAREA-name)
|((LOB-column-storage-RDAREA-name)
[,(LOB-column-storage-RDAREA-name)]...)
|matrix-partitioned-LOB-column-storage-RDAREA-specification}
|abstract-data-type-LOB-column-storage-
RDAREA-specification}}]
[plug-in-specification]
[DEFAULT clause]
[column-restriction...]
[updatable-column-attribute]
column-name
Specifies a name for a column that is to compose the table. Each column name must be unique.
data-type
Specifies the data type of the column; see 1.2 Data types for an explanation of data types.
Neither a super-type abstract data type with BLOB defined nor the BOOLEAN data type can be specified.
If the specified data type is an abstract data type, no authorization identifier is specified, and the default authorization identifier does not have an abstract data type of the same name, and if there is an abstract data type of the same name in the 'MASTER' authorization identifier, that abstract data type is assumed to have been specified.
ARRAY [max-number-of-elements]
When a repetition column is being defined, specifies the maximum number of elements, as an unsigned integer in the range 2 to 30,000.
A repetition column cannot be specified for any of the following data types:
  • BLOB
  • BINARY
  • Abstract data type
NO SPLIT
Specifies that when the actual data length of a variable-length character string exceeds 255 bytes, one row of data is to be stored on a single page. In some cases, the NO SPLIT option will reduce the database's storage space requirements. This is called the no-split option; for details of the no split option, see the HiRDB Version 8 Installation and Design Guide.
The no-split option is applicable only to variable-length character string data types (VARCHAR, NVARCHAR, and MVARCHAR).
LOB-column-storage-RDAREA-name
Specifies the name of the user LOB RDAREA for storing BLOB column data.
The following rules apply to LOB column storage RDAREAs:
  1. If the BLOB data type is specified for a column, a LOB column storage RDAREA must be specified for it. A LOB column storage RDAREA cannot be specified for a column of a non-BLOB data type.
  2. When a table is partitioned, you must specify the same number of user LOB RDAREAs as the number of partitions into which the table is to be split. Thus, the partitioning must be specified so that the user RDAREAs and the user LOB RDAREAs at the same server will be in the same sequence. An example is shown below:

            CREATE TABLE MOVIE (ID INT NOT NULL,
                   IMAGE BLOB IN ((LU01),(LU02))
                   IN ((RU01) ID<120,(RU02))

LU01, LU02, RU01, and RU02 denote RDAREA names.
RU01 and LU01, and RU02 and LU02, are RDAREAs for the respective servers.
  1. Before CREATE TABLE can be execute LOB column storage RDAREA must be created using the database initialization utility or must be added using the database structure modification utility.
  2. LOB column storage RDAREA names cannot be specified in duplicate. If there are duplicate table storage RDAREA names in a partitioned table with a boundary value specification or in a matrix-partitioned table, specify corresponding LOB storage RDAREA names.
matrix-partitioned-LOB-column-storage-RDAREA-specification::=two-dimensional-storage-RDAREA-specification
This operand is specified when defining a matrix-partitioned table.
For specification methods, see the explanation of two-dimensional storage RDAREA names in PARTITIONED BY MULTIDIM. For details about RDAREAs that can be specified, see the explanation of LOB column storage RDAREA names.
8) column-data-suppression-specification::=[SUPPRESS]

Specifies that column data is to be suppressed. This option reduces the amount of disk space that is required when much of the data is in fixed-size character format with trailing spaces.

The following rules apply to column data suppression specifications:

  1. If the last character in column data is the space, a search is made for contiguous spaces to the left and the data is stored in the database by suppressing the string of consecutive spaces that are found as a result of this search. If there is a break in the string of consecutive spaces, no spaces to the left of the break are suppressed.
  2. When the data type is CHAR or MCHAR, four or more one-byte spaces are suppressed.
  3. When the data type is NCHAR, three or more two-byte spaces are suppressed.
  4. This option cannot be specified for a FIX table.
  5. Column data suppression cannot be specified if the data type is an abstract data type.
  6. This option can be specified only when the data type is CHAR, MCHAR, or NCHAR.
  7. Column data suppression cannot be specified for a repetition column.
  8. One byte of additional information is added to a column for which this option is specified even if data suppression does not occur.
  9. Specification of this option is invalid in the following cases:
    • Data type is CHAR and the column data size is shorter than CHAR(5)
    • Data type is MCHAR and the column data size is shorter than MCHAR(5)
    • Data type is NCHAR and the column data size is shorter than NCHAR(3)
    If spaces are exhausted, any space preceding that location is not suppressed.
9) column-recovery-restriction ::=[RECOVERY [{ALL|PARTIAL|NO}]]

Specifies the database update logging method for a LOB column storage RDAREA or a LOB storage RDAREA within an abstract data type definition.

LOB column storage RDAREA:
If the BLOB data type is specified, specifies the database update logging method for user LOB RDAREAs. This option cannot be specified for columns with a data type other than BLOB.
LOB storage RDAREA within an abstract data type definition:
If the BLOB attribute is in an abstract data type definition, specifies the database update logging method for user LOB RDAREAs.
ALL
Specifies that the user LOB RDAREA is to be operated in the log acquisition mode. Operation in this mode means that a database update log needed for rollback and rollforward is acquired.
PARTIAL
Specifies that the user LOB RDAREA is to be operated in the pre-update log acquisition mode. Operation in this mode means that a database update log needed for rollback is acquired.
NO
Specifies that the user LOB RDAREA is to be operated in the no-log mode. Operation in this mode means that a database update log is not to be acquired.
The UAP execution method and the method of recovering a user LOB RDAREA when an error occurs depend on the specified update log acquisition mode for the database. For details of operation in the no-log mode, see the HiRDB Version 8 System Operation Guide.
10) abstract-data-type-LOB-column-storage-RDAREA-specification::=
ALLOCATE (attribute-name[..attribute-name]...
IN {LOB-attribute-storage-RDAREA-name
|(LOB-attribute-storage-RDAREA-name)
|((LOB-attribute-storage-RDAREA-name)
[, (LOB-attribute-storage-RDAREA-name)])
|matrix-partitioned-LOB-attribute-storage-RDAREA-specification}
[, attribute-name[..attribute-name]...
IN {LOB-attribute-storage-RDAREA-name
|(LOB-attribute-storage-RDAREA-name)
|((LOB-attribute-storage-RDAREA-name)
[, (LOB-attribute-storage-RDAREA-name)])
|matrix-partitioned-LOB-attribute-storage-RDAREA-specification}]...)
attribute-name[..attribute-name]
Specifies the names of attributes that comprise an abstract data type. If the attribute of the abstract data type is the abstract data type and if the attribute of nested abstract data types includes a LOB-type attribute, specify the attribute name of the LOB type.
Specify an attribute name in the following cases:
  • If the data type of the attribute of an abstract data type is the LOB type
  • If the attribute of an abstract data type is the abstract data type and if the attribute of nested abstract data types is the LOB-type attribute (specify the attribute name of that LOB type)
LOB-attribute-storage-RDAREA-name
Specifies the name of a user LOB RDAREA for storage of BLOB-attribute data, located at any level of the abstract data type.
The following rules apply to LOB attribute storage RDAREAs:
  1. If an abstract data type with the BLOB type is specified as a data type, a LOB RDAREA must be specified for each BLOB attribute. A LOB attribute storage RDAREA cannot be specified for an attribute whose data type is not of the BLOB type.
  2. When a table is being partitioned, specify the same number of user LOB RDAREAs as the number of partitions into which the table is to be partitioned. You must ensure that the user RDAREAs and user LOB RDAREAs on the same server are specified in the same order.
  3. Required LOB attribute storage RDAREAs must be created or added in advance with the database initialization utility or the database structure modification utility.
  4. The operand LOB-attribute-storage-RDAREA-name cannot be specified in duplicate. However, if there are duplicate partitioned tables with a boundary value specification or table storage RDAREA names in a matrix-partitioned table, specify corresponding LOB attribute storage RDAREA names.
matrix-partitioned-LOB-attribute-storage-RDAREA ::=two-dimensional-storage-RDAREA-specification
This operand is specified when defining a matrix-partitioned table.
For specification methods, see the explanation of two-dimensional storage RDAREA names in PARTITIONED BY MULTIDIM. For details about RDAREAs that can be specified, see the explanation of LOB attribute storage RDAREA names.
11) plug-in-specification ::= PLUGIN plug-in-option
plug-in-option
Specifies as a character string literal (of up to 255 bytes) parameter information to be passed to the plug-in facility for a column that is defined as an abstract data type for which the plug-in facility is implemented. Hexadecimal character string literals cannot be specified as parameter information. For details of the parameter information, see the manual for the applicable plug-in.
12) DEFAULT clause ::=DEFAULT[predefined-value]

If a value is omitted during the insertion of data, the value that was specified in DEFAULT clause is assumed.

The following rules apply to DEFAULT clause:

  1. The DEFAULT clause and the WITH DEFAULT clause cannot be specified for the same item.
  2. The DEFAULT clause cannot be specified for items of the BLOB type, the abstract data type, or the BINARY type with a definition length of 32,001 bytes or greater.
  3. The DEFAULT clause cannot be specified for a repetition column.
predefined-value ::={literal|USER|CURRENT_DATE|CURRENT DATE
|CURRENT_TIME|CURRENT TIME
|CURRENT_TIMESTAMP[(fractional-second-precision)][USING BES]
|CURRENT TIMESTAMP[(fractional-second-precision)][USING BES]
|NULL}
The default value must be a value that can be inserted into a specified item.
The following rules apply to predefined-value:
  1. If predefined-value is omitted, the predefined value for WITH DEFAULT is assumed.
  2. In predefined-value, a data type in which the effective upper digits are nullified during insertion cannot be specified.
  3. NULL as a predefined value cannot be specified in a NOT NULL constrained column.
  4. If USER, CURRENT_DATE(current-date), CURRENT_TIME(current-time), or CURRENT_TIMESTAMP[(fractional-second-precision)] (CURRENT TIMESTAMP[(fractional-second-precision)]) is specified, the following value is assigned:
  • USER:
    The value of the authorization identifier of the execution user who inserted the row is assigned.
  • CURRENT_DATE(current-date):
    The date of row insertion is assigned. The database load utility (pdload), however, assigns the date the utility is started.
  • CURRENT_TIME(current-time):
    The time of row insertion is assigned. The database load utility (pdload), however, assigns the time the utility is started.
  • CURRENT_TIMESTAMP [(fractional-second-precision) [USING BES]] (CURRENT TIMESTAMP [(fractional-second-precision)] [USING BES])
    Assigns the time stamp at the time a row was inserted. However, for the database load utility (pdload), the time stamp at the time the utility was started is assigned. In operations using a multi-front-end server configuration on HiRDB/Parallel Server, the time stamp is obtained from the front-end server to which the UAP established a connection.
    If USING BES is specified, the current time stamp is acquired from the back-end server that manages the RDAREA in which update rows or insertion rows are stored in the case of a HiRDB/Parallel Server; for a HiRDB/Single Server, the current time stamp is acquired from the single server.
    If USING BES is omitted, the current time stamp is acquired from the front-end server in the case of a HiRDB/Parallel Server; and the current time stamp is acquired from the single server in the case of a HiRDB/Single Server.
    In the default-value acquisition server type specification, a column for which USING BES is specified cannot be specified in partitioning-key.
13) column-restriction ::= {NOT-NULL-constraint|single-column-uniqueness-constraint-definition| [index-option [index-option]]}
| {single-column-check-constraint-definition [constraint-name-definition]
| [constraint-name-definition] single-column-check-constraint-definition}
| {single-column-referential-constraint-definition [constraint-name-definition]
| [constraint-name-definition] single-column-referential-constraint-definition}}

The following restrictions can be specified for a column:

The position in which constraint-name-definition is specified is determined by the specification value in the system common definition pd_constraint_name operand or the specification value in the client environment variable PDCNSTRNTNAME. For details, see Table 3-28 on table constraint definitions.

A single-column referential constraint cannot be specified if both SHARE and FIX are specified.

14) updatable-column-attribute:: = UPDATE [ONLY FROM NULL]
Specify this operand when defining a falsification-prevented table or defining an updatable column in a table that will be changed into a falsification-prevented table.
The updatable column attribute is valid only with a falsification-prevented table.
For details about the falsification-prevented table, see the INSERT ONLY option. For details about how to change a given table into a falsification-prevented table, see the INSERT ONLY option in CHANGE in ALTER TABLE.
The following rules apply to the updatable clause attribute:
  1. The attribute cannot be specified for columns for which SYSTEM GENERATED is specified.
  2. The attribute cannot be specified for any of the following non-updatable columns:
[Figure]Cluster key constituent column
[Figure]Partitioning key constituent column (exclusive of partitioning key constituent columns in a flexible hash partitioning table)
UPDATE
Specify this operand when defining an updatable column in a falsification-prevented table.
UPDATE ONLY FROM NULL
Specify this operand when defining a column in a falsification-prevented table in which row values can be updated only once from the null value to a non-null value.
The following table shows the updatability of column values in a falsification-prevented table for which UPDATE ONLY FROM NULL is specified.
Column value before updateColumn value after updateUpdatability
Null valueNull valueY
Null valueNon-null valueY
Non-null valueNull valueN
Non-null valueNon-null value*N
Legend:
Y: Updatable
N: Not updatable
Note
Repetition columns can be updated only from the null value (a value in which the number of elements is 0) to unsubscripted columns.
* Includes the same value as a pre-update value.
The following rules apply to the UPDATE ONLY FROM NULL operand :
  • This operand cannot be specified for columns for which NOT NULL is specified.
  • This operand cannot be specified for FIX tables.
  • This operand cannot be specified for the primary key or for cluster key constituent columns.
  • This operand cannot be specified for partitioning key constituent columns.
  • This operand cannot be specified for BLOB type columns and for BINARY type columns with a minimum definition length of 32,001 bytes or greater.
If the attribute is specified, the column value of the updatable column attribute can be updated under the following conditions:
Table typeUPDATE specificationUPDATE ONLY FROM NULL specificationNo specification
SpecifiableColumn value updatableSpecifiableColumn value updatableSpecifiableColumn value updatable
Non-falsification-prevented tableYYYY[Figure]Y
Falsification-prevented tableYYYY*[Figure]N
Legend:
Y: Updatable
N: Not updatable
[Figure]: Not applicable
* Can be updated only once from the null value to a non-null value.
15) NOT-NULL-constraint-specification::=
{[NULL
|NOT NULL [WITH DEFAULT[SYSTEM GENERATED]]]2
|[NOT NULL] WITH DEFAULT[SYSTEM GENERATED]]}1
1For a column of a FIX table, a cluster key column, or a column that belongs to the primary key.
2 For a column other than the above.
NULL
Specifies that the null value is to be permitted in the specified column.
The NULL option cannot be specified for a column of a FIX table, a cluster key column, or a column that belongs to the primary key.
NOT NULL
Specifies the NOT NULL constraint, which means that the column cannot contain the null value.
NOT NULL cannot be specified for an abstract data type or a repetition column.
WITH DEFAULT
If the column names to be inserted and the insertion values are omitted during data loading using either the INSERT statement or the database creation utility, the WITH DEFAULT option must be specified when the default values are inserted into NOT NULL constrained columns.
The following rules apply to the WITH DEFAULT option:
  1. NOT NULL can be omitted when specifying WITH DEFAULT for a FIX table.
  2. WITH DEFAULT cannot be omitted if the data type is the abstract data type.
  3. The following table shows column default values that are assigned when WITH DEFAULT is specified.

    Table 3-29 Default values for a column with the WITH DEFAULT clause

    Data typeColumn default value
    INTEGER0
    SMALLINT
    FLOAT
    SMALLFLT
    DECIMAL
    CHARSpace
    NCHAR
    MCHAR
    VARCHAR1-byte space
    NVARCHAR1-character space
    MVARCHAR1-byte space
    DATECurrent date when a row is added
    TIMECurrent time when a row is added
    TIMESTAMPCurrent time stamp when a row is added
    INTERVAL YEAR TO DAY0 years, 0 months, 0 days
    INTERVAL HOUR TO SECOND0 hours, 0 minutes, 0 seconds
    BLOBData with a length of 0 bytes
    BINARY
Note
When the WITH DEFAULT clause is not specified, the null value becomes the default value for the column.
SYSTEM GENERATED
This option can be specified when the data type of the column is either the DATE type or TIME type. The column for which SYSTEM GENERATED is specified is called an insert history maintenance column. Columns for which SYSTEM GENERATED is specified are used to specify a deletion-prevented duration for a falsification-prevented table.
Columns for which SYSTEM GENERATED is specified receive the insertion of the current date (CURRENT_DATE) for the DATE type, or the current time (CURRENT_TIME) for the TIME type during the insertion of data by means of an INSERT statement, irrespective of whether a value is specified.
16) single-column-uniqueness-constraint-definition::=
{[{UNIQUE |PRIMARY}] CLUSTER KEY [{ASC |DESC}]
[IN {index-storage-RDAREA-name
|(index-storage-RDAREA-name)
|((index-storage-RDAREA-name)
[, (index-storage-RDAREA-name)]...)}]
|PRIMARY KEY [{ASC |DESC}]
[IN {index-storage-RDAREA-name
|(index-storage-RDAREA-name)
|((index-storage-RDAREA-name)
[, (index-storage-RDAREA-name)]...)}]}
{[{UNIQUE |PRIMARY}] CLUSTER KEY [{ASC |DESC}]
[IN {index-storage-RDAREA-name
|(index-storage-RDAREA-name)
|((index-storage-RDAREA-name)
[, (index-storage-RDAREA-name)]...)}]
Specifies that the column is to be defined as a cluster key.
The following rules apply to the cluster key:
  1. None of the following data types can be specified for a column that composes a cluster key:
    [Figure]DECIMAL with a precision exceeding 19
    [Figure]BLOB
    [Figure] BINARY
    [Figure] Abstract data type
  2. A repetition column cannot be specified as a column that composes a cluster key.
  3. When a cluster key is specified, an index is defined for the specified column. A defined index cannot be deleted.
  4. The NOT NULL option is assumed for a cluster key column.
  5. Duplicated data cannot be inserted into a cluster key for which UNIQUE or PRIMARY is specified.
  6. Only one cluster key can be defined per table.
  7. When a cluster key is specified, the HASHA to HASHF hash functions cannot be specified.
  8. The total length of columns comprising a cluster key must satisfy the following formula:
    Total length of columns
    [Figure]MIN((page size of index storage RDAREAs[Figure] 2) - 1242, 4036)
  9. The following rules apply to specifying a cluster key on a partitioned table:
key/range-partitioning (storage-condition, boundary-value)
Specify partitioning keys for columns that compose a cluster key.
hash-partitioning
single-column-partitioning
Specify partitioning keys for columns that compose a cluster key.
multicolumn-partitioning
A cluster key cannot be specified for a single column.
matrix-partitioning
A cluster key cannot be specified for a single column.
UNIQUE
Specifies the restriction that the value in each row in the cluster key column must be unique (i.e., no value can be duplicated in the cluster key column).
PRIMARY
Specifies that a column belonging to the cluster key is being defined as the primary key.
ASC
Specifies that the cluster key index is to be generated in ascending order of the key values.
DESC
Specifies that the cluster key index is to be generated in descending order of the key values.
index-storage-RDAREA-name
Specifies the name of an RDAREA in which the cluster key index is to be stored.
If the index is to be partitioned by rows for storage, an index storage RDAREA name must be specified for each component into which the table is to be partitioned.
On HiRDB/Parallel Server, the operand is subject to the following restrictions:
  • If SHARE is specified, the index storage RDAREA name should be a shared RDAREA.
  • If SHARE is not specified, the index storage RDAREA name cannot be specified as a shared RDAREA.
The following rules apply to the index storage RDAREAs:
  1. Each specified RDAREA either must have already been created by the database initialization utility or must be added by the database structure modification utility.
  2. If no index storage RDAREA names are specified, the index is stored in the same RDAREAs that store the defined table.
  3. When multiple index storage RDAREA names are specified, the same RDAREA name cannot be specified more than once. However, if there are duplicate table storage RDAREA names in a partitioned table with a boundary-value specification or in a matrix-partitioned table, specify corresponding index storage RDAREA names.
  4. A user RDAREA that stores a rebalancing table cannot be specified. Similarly, if a cluster key is defined for a rebalancing table, the specification of an RDAREA name cannot be omitted.
  5. The number of index storage RDAREA names must equal the number of table partitions that are stored (tables are partitioned by rows). In this case, the specified index storage RDAREAs are used in the same order in which the table storage RDAREAs are specified. All specified RDAREAs must be in the same server.
    Examples are provided below:
Example 1: Partitioning by rows based on storage conditions
[Figure]
Example 2: Partitioning by rows based on a boundary value
[Figure]
RDA1 to RDA6 denote RDAREA names.
The arrows indicate that RDAREAs RDA4, RDA5, and RDA6 of the RDAREAs in the table are associated with RDAREAs RDA1, RDA2, and RDA3 of the index, respectively.
PRIMARY KEY [{ASC |DESC}]

  [IN {index-storage-RDAREA-name
      |(index-storage-RDAREA-name)
      |((index-storage-RDAREA-name)
        [, (index-storage-RDAREA-name)]...)}]

Specifies that the column specified in column-name is being defined as the primary key.
The following rules apply to the primary key:
  1. The following data types cannot be specified for the columns that make up the primary key:
    [Figure]DECIMAL with a precision greater than 19 digits
    [Figure]BLOB
    [Figure] BINARY
    [Figure] Abstract data type
  2. A repetition column cannot be specified as a column comprising the primary key.
  3. Specifying a primary key causes an index to be defined for the specified columns. An index defined in this manner cannot be deleted.
  4. NOT NULL is assumed for a column that comprises the primary key.
  5. Duplicated data cannot be inserted into a primary key column.
  6. Only one primary key can be defined per table.
  7. For definability (UNIQUE specifiability) of row-partitioned tables, see Table 3-20.
  8. The total length of columns comprising the primary key must satisfy the following formula:
    Total length of columns
    [Figure]MIN((page size of index storage RDAREAs[Figure] 2) - 1242, 4036)
  9. For a matrix-partitioned table, the primary key cannot be specified for a single column.
ASC
Specifies that the primary key index is to be created in ascending order.
DESC
Specifies that the primary key index is to be created in descending order.
index-storage-RDAREA-name
Specifies the name of an RDAREA for storing the primary key index.
The specified index storage RDAREA should be a user RDAREA.
On HiRDB/Parallel Server, the operand is subject to the following restrictions:
  • If SHARE is specified, the index storage RDAREA name should be a shared RDAREA.
  • If SHARE is not specified, the index storage RDAREA name cannot be specified as a shared RDAREA.
The following rules apply to the index storage RDAREAs:
  1. The RDAREA must be created in advance using the database initialization utility or added using the database structure modification utility.
  2. If no RDAREA name is specified, the index will be stored in the RDAREAs that store the table that is being defined. However, in the case of a row-partitioned table in a HiRDB/Single Server or a row-partitioned table at one back-end server in a HiRDB/Parallel Server, the index will be stored as follows:
    For a single-column partitioned table
    If a column other than the partitioning key is specified as the primary key, the index is stored in the first table storage RDAREA for which a partitioning condition is specified.
    For a multicolumn partitioned table
    The index is stored in the first table storage RDAREA for which a partitioning condition is specified.
    Matrix-partitioned tables
    The primary key cannot be specified for a single column.
  3. When multiple index storage RDAREA names are specified, the same RDAREA name cannot be specified more than once. However, if there are duplicate table storage RDAREA names in a partitioned table with a boundary-value specification or in a matrix-partitioned table, this operand can be specified.
  4. A user RDAREA that stores a rebalancing table cannot be specified. Similarly, if a cluster key is defined for a rebalancing table, specification of an RDAREA name cannot be omitted.
  5. If a table is stored on a split basis into multiple RDAREAs, the index storage RDAREAs must be specified by one of the following methods:
    (a) Specifying a column specified as a partitioning key in a single-column-partitioned table, at the beginning of the primary key
    (b) Specifying all columns specified as a partitioning key in a multicolumn-partitioned table, in the same order at the beginning of the primary key
    (c) Matrix-partitioned tables
    (d) Other than items (a) to (c), specifying all columns specified in a partitioned key as constituent columns of the primary key (in any order)
    For items (a) to (c), specify RDAREA names in a number equal to the number of table storage RDAREA names. In this case, the destination of index storage corresponds with the order in which table storage RDAREAs are specified.
    For (d), specify RDAREA names in a number equal to the number of servers that are stored by partitioning tables. For a HiRDB/Single Server, specify only one RDAREA name; for a HiRDB/Parallel Server, specify an RDAREA per back-end server that contains a table.
    In any other cases, the primary key cannot be defined for a table that is stored on a partitioned basis.
17) index-option ::={PCTFREE=percentage-of-free-area|UNBALANCED SPLIT}
PCTFREE =percentage-of-free-area
Specifies the percentage of unused space to be left in index pages when an index is created. The specifiable range of values is 0 to 99, and the default is 30.
When created in batch by the database load utility and the database reorganization utility, indexes are created in a percentage equal to the percentage of unused space. In other addition or update operations by INSERT or UPDATE statements, the default PCTFREE=0 is assumed.
If rows are to be added frequently after an index has been created, a high percentage of unused space should be specified.
UNBALANCED SPLIT
Specifies that the key values are to be allocated unevenly among the pages when a page is split.
If the location where a key value is to be inserted is in the first half of the page to be split, more empty space is allocated to the left-side page after the split. If the key value insertion location is in the second half of the page, more empty space is allocated to the right-side page after the split. This is called an unbalanced index split.
For details of unbalanced index splits, see the HiRDB Version 8 System Operation Guide.
18) single-column-check-constraint-definition::= CHECK (search-condition)
Specify this operand when defining a check constraint for the column specified in column-name.
search-condition
Specifies the condition that constrains the value of a column. If this condition is FALSE, no insertion or updating can be performed on the table.
The column specified in search-condition should be one that was specified in column-name.
The following items cannot be specified in search-condition:
  • Subquery
  • Set function
  • Window function
  • Repetition column
  • Function call
  • USER
  • CURRENT DATE, CURRENT_DATE
  • CURRENT TIME, CURRENT_TIME
  • CURRENT TIMESTAMP, CURRENT_TIMESTAMP
  • ? parameter, embedded variable
  • CAST specification specifying conversion from TIME to TIMESTAMP
  • Scalar function VARCHAR_FORMAT specifying TIME type in a value expression
  • Value expression in the abstract data type
  • System-defined scalar function
  • Scalar function IS_USER_CONTAINED_IN_HDS_GROUP
  • Structured repetition predicate
19) single-column-referential-constraint-definition::= reference-specification
Specify this operand when defining the column specified in column-name as a foreign key.
The following rules apply to foreign keys:
  1. If a foreign key is specified for a column, the following data types cannot be specified on the column:
    [Figure]DECIMAL with a minimum precision of 20
    [Figure]BLOB
    [Figure] BINARY
    [Figure] Abstract data type
  2. A repetition column cannot be specified for a foreign key column.
  3. The foreign key and the primary key that references it must agree in all of the following items:
    [Figure]Corresponding data type
    [Figure]Corresponding data length
20) multicolumn-uniqueness-constraint-definition::=
{[{UNIQUE |PRIMARY}] CLUSTER KEY (column-name [{ASC |DESC}]
[, column-name [{ASC|DESC}]] ...)
[IN {index-storage-RDAREA-name
|(index-storage-RDAREA-name)
|(index-storage-RDAREA-name)
[,(index-storage-RDAREA-name)]...)
|matrix-partitioned-index-storage-RDAREA-specification}]
|PRIMARY KEY [{ASC |DESC}]
[IN {index-storage-RDAREA-name
|(index-storage-RDAREA-name)
|((index-storage-RDAREA-name)
[, (index-storage-RDAREA-name)]...)
|matrix-partitioned-index-storage-RDAREA-specification}]}
{[{UNIQUE |PRIMARY}] CLUSTER KEY (column-name [{ASC |DESC}]
[, column-name [{ASC|DESC}]] ...)
[IN {index-storage-RDAREA-name
|(index-storage-RDAREA-name)
|(index-storage-RDAREA-name)
[,(index-storage-RDAREA-name)]...)
|matrix-partitioned-index-storage-RDAREA-specification}]
Specifies that multiple columns are to be defined as a cluster key.
The following rules apply to cluster keys:
  1. None of the following data types can be specified for the columns that comprise a cluster key:
    [Figure]DECIMAL with a precision exceeding 19
    [Figure]BLOB
    [Figure] BINARY
    [Figure] Abstract data type
  2. If a cluster key is comprised of multiple columns, the following data types, in addition to item 1, cannot be specified:
    [Figure]FLOAT
    [Figure] SMALLFLT
  3. A repetition column cannot be specified as a column that composes a cluster key.
  4. When a cluster key is specified, an index is defined for the specified columns. When a defined index is deleted, the associated table should also be deleted.
  5. The NOT NULL option is assumed for the columns that comprise a cluster key.
  6. Duplicated data cannot be inserted into a cluster key for which UNIQUE or PRIMARY is specified.
  7. Only one cluster key can be defined per table.
  8. The maximum number of columns comprising a cluster key for which PRIMARY is specified is 16.
  9. When a cluster key is specified, the HASHA to HASHF hash functions cannot be specified.
  10. The total length of columns comprising an index must satisfy the following formula:
    Total length of columns
    [Figure]MIN((page size of index storage RDAREAs[Figure] 2) - 1242, 4036)
  11. The following rules apply to specifying a cluster key for a partitioned table:
key/range-partitioning (storage-condition, boundary-value)
Specify partitioning keys for columns that compose a cluster key.
hash-partitioning
single-column-partitioning
Specify partitioning keys at the beginning of the columns that compose a cluster key.
multicolumn-partitioning
Specify this item so that it includes all partitioning keys in the same sequence, from the beginning of the columns that compose a cluster key.
Matrix-partitioning
Specify this item so that it includes all partitioning keys in a first-dimension partitioned column and second-dimension partitioned column sequence from the beginning of the columns that compose a cluster key.
When multicolumn-partitioning a second-dimension partitioned clause, specify this item so that it includes all partitioning keys in a first-dimension partitioned column - second-dimension partitioned column sequence.
UNIQUE
Specifies the restriction that the value in each row in the cluster key column must be unique (i.e., no value can be duplicated in the cluster key column).
PRIMARY
Specifies that a multicolumn cluster key is being defined as the primary key.
column-name
Specifies the name of a column that defines the cluster key.
The names of all columns that comprise the cluster key must be unique. When a cluster key is defined to consist of multiple columns, the key values are generated in the order in which the columns are specified.
ASC
Specifies that the index of the cluster key values is to be generated in ascending order of the values.
DESC
Specifies that the index of the cluster key values is to be generated in descending order of the values.
index-storage-RDAREA-name
Specifies the name of an RDAREA in which the cluster key index is to be stored.
If the index is to be partitioned by values for storage, an index storage RDAREA name must be specified for each component into which the table is partitioned.
The specified index storage RDAREAs must be user RDAREAs.
The following rules apply to the index storage RDAREAs:
  1. Each specified RDAREA either must have been created by the database initialization utility or must be added by the database structure modification utility.
  2. If no index storage RDAREA names are specified, the index is stored in the same RDAREAs that store the defined table.
  3. When multiple index storage RDAREA names are specified, the same RDAREA name cannot be specified more than once. However, if there are duplicate table storage RDAREA names in a partitioned table with a boundary-value specification or in a matrix-partitioned table, specify corresponding index storage RDAREA names.
  4. The number of index storage RDAREAs must equal the number of table partitions that are stored (tables are partitioned by rows). In this case, the specified index storage RDAREAs are used in the same order in which the table storage RDAREAs are specified. All specified RDAREAs must be in the same server.
    For an example, see the section on index storage RDAREA names for the single-column uniqueness restriction definition.
  5. A user RDAREA that stores a rebalancing table cannot be specified. Similarly, if a cluster key is defined for a rebalancing table, specification of an RDAREA name cannot be omitted.
matrix-partitioned-index-storage-RDAREA-specification::=two-dimensional-storage-RDAREA-specification
This operand is specified when defining a matrix-partitioned table.
For specification methods, see the explanation of two-dimensional storage RDAREA names in PARTITIONED BY MULTIDIM. For details about RDAREAs that can be specified, see the explanation of index storage RDAREA names.
PRIMARY KEY (column-name [{ASC |DESC}]
[, column-name [{ASC |DESC}]]...)
[IN {index-storage-RDAREA-name
|(index-storage-RDAREA-name)
|((index-storage-RDAREA-name)
[,(index-storage-RDAREA-name)]...)
|matrix-partitioned-index-storage-RDAREA-specification}]
Specifies multiple columns that are being defined as the primary key.
The following rules apply to the primary key:
  1. The following data types cannot be specified for the columns that make up the primary key:
    [Figure]DECIMAL with a precision greater than 19 digits
    [Figure]BLOB
    [Figure] BINARY
    [Figure] Abstract data type
  2. When the primary key is composed of multiple columns, the following data types, in addition to item 1, cannot be specified:
    [Figure]FLOAT
    [Figure] SMALLFLT
  3. A repetition column cannot be specified as a column comprising the primary key.
  4. Specifying a primary key causes an index to be defined for the specified columns. To delete the defined index, the entire table must be deleted.
  5. NOT NULL is assumed for a column that comprises the primary key.
  6. Duplicated data cannot be inserted into a primary key column.
  7. Only one primary key can be defined per table.
  8. The maximum number of columns comprising a cluster key for which PRIMARY is specified is 16.
  9. For definability of the primary key (UNIQUE specifiability) for row-partitioned tables, see Table 3-20 Specifiability of UNIQUE in conjunction with row-partitioning of a table.
  10. The total length of columns comprising an index must satisfy the following formula:
    Total length of columns
    [Figure]MIN((page size of index storage RDAREAs [Figure]2) - 1242, 4036)
column-name
Specifies the name of a column for which the primary key is to be defined.
The column names that comprise the primary key must all be unique. When multiple columns are defined for the primary key, the key values are created in the order in which the columns are specified.
ASC
Specifies that the primary key index is to be created in ascending order.
DESC
Specifies that the primary key index is to be created in descending order.
index-storage-RDAREA-name
Specifies the name of the RDAREA that stores the index for the primary key.
  1. If index-storage-RDAREA-name is omitted, the index also is stored in the RDAREA that stores the defined table. However, for a row-partitioned table on HiRDB/Single Server, or for a row-partitioned table on the same back-end server on HiRDB/Parallel Server, indexes are stored as follows:
    For a single-column-partitioned table:
    The RDAREA that stores the defined table also stores the index.
    For a multicolumn-partitioned table:
    The multicolumn-partitioned table is stored in the first table storage RDAREA for which partitioning conditions are specified, unless all columns that are specified as partitioning keys are specified in the same order from the beginning of the primary key.
    For a matrix-partitioned table:
    The table cannot be defined unless all columns that are specified as partitioning keys are specified in the same order from the beginning of the primary key. If all columns that are specified as partitioning keys are not specified from the beginning of the primary key in the same order, specify the name of an index storage RDAREA.
For an explanation of index storage RDAREA names and rules regarding those names, see the explanation of index storage RDAREA names in PRIMARY KEY of the single-column uniqueness constraint definition.
matrix-partitioned-index-storage-RDAREA-specification::=two-dimensional-storage-RDAREA-specification
This operand is specified when defining a matrix-partitioned table.
For details about how to specify this, see the explanation of two-dimensional storage RDAREA names in PARTITIONED BY MULTIDIM. For details about the RDAREA name to be specified, see the explanation of index storage RDAREA names for [{UNIQUE|PRIMARY}] CLUSTER KEY for the multicolumn uniqueness constraint definition.
21) multicolumn-check-constraint-definition::= CHECK(search-condition)
Specify this operand when defining a check constraint for multiple columns.
search-condition
Specifies the condition under which multiple columns are restricted. If this condition is FALSE, no insertions or updates can be performed on the table.
In search-condition, specify the column that was specified in the table definition.
For restrictions in a search condition, see search-condition in 18) Single-column-check-constraint-definition.
22) multicolumn-referential-constraint-definition::= FOREIGN KEY(column-name [, column-name]...) reference-specification
Specify this operand when defining multiple columns as a foreign key.
The following rules apply to foreign keys:
  1. When a foreign key is specified, the following data types cannot be specified for columns that compose the foreign key:
    [Figure]DECIMAL with a minimum precision of 20
    [Figure]BLOB
    [Figure] BINARY
    [Figure] Abstract data type
  2. A repetition column cannot be specified for a column that composes a foreign key.
  3. The foreign key and the primary key that is referenced must be identical in all of the following items:
    [Figure]Corresponding data type
    [Figure]Corresponding data length
    [Figure]Number of columns
    If more than one column is specified as a foreign key, HiRDB checks the correspondence between columns in the order in which they are specified.
FOREIGN KEY (column-name [, column-name]...) reference-specification
Specifies the names of columns that compose a foreign key.
A maximum of 16 column names can be specified.
All specified column names must be distinct.
23) storage-condition ::=
column-name {= |< > | ^= |!= |< |<= | > | > =} {literal |(literal[, literal])}

Specifies conditions for storing the table in multiple RDAREAs on a split basis (row-partitioning of the table).

The following rules apply to the storage conditions:

  1. Multiple literals can be specified only when the = comparison operator is used.
  2. When multiple literals are specified, the same value cannot be specified more than once.
  3. Columns with the following data types can be used in comparison operations:
    • INTEGER
    • SMALLINT
    • DECIMAL
    • FLOAT
    • SMALLFLT
    • CHARACTER1
    • VARCHAR1
    • NCHAR2
    • NVARCHAR2
    • MCHAR1
    • MVARCHAR1
    • DATE
    • TIME
    • TIMESTAMP4
    • INTERVAL YEAR TO DAY3
    • INTERVAL HOUR TO SECOND3
      1 Comparison operation can be specified only on columns with a defined length not exceeding 255 bytes.
      2 Comparison operation can be specified only on columns with a defined length not exceeding 127 characters.
      3 The DCVALUES column in the SQL_DIV_TABLE data dictionary table (storage assignment condition value) contains corrected values:
      Examples
      19921225.[Figure]19930025.
      99981315.[Figure]99990115.
      4 This item cannot be specified if the fractional second precision is greater than 0. Also, it cannot be specified if a default value including USING BES is specified in the DEFAULT clause.
  4. The following items cannot be specified in literal:
    • Character string literals, national character string literals, or mixed character string literals with a length of 0.
    • Character string literals with a length of 256 bytes or greater, national character string literals with a length of 128 characters or greater, or mixed character string literals with a length of 256 bytes or greater
    • Hexadecimal character string literals
  5. The value specified in column-name cannot be updated.
  6. The number of literals specified in each storage condition must be such that the total number of literals specified in all storage conditions does not exceed 5,000. If a storage condition is omitted, the number of literals used is counted as 1.
  7. If a cluster key is specified for a column, storage conditions cannot be specified for any other columns.
  8. The column specified in column-name must be NOT NULL (NOT NULL constraint, FIX specification, or a cluster key).
  9. When a multicolumn cluster key is specified, a storage condition cannot be specified for columns other than the leading column.
  10. A storage condition cannot be specified for a repetition column.
24) hash-function -name::=
{HASH1 |HASH2 |HASH3 |HASH4 |HASH5 |HASH6 |HASH0 |HASHA |HASHB |HASHC |HASHD |HASHE |HASHF}
If the table is not a rebalancing table, or when a hash function is specified in the second dimension of a matrix-partitioned table:
Specify one of the hash functions HASH1 through HASH6 or HASH0.
HASH6 is usually specified, because it provides the most uniform hashing. However, some partitioning key data is not amendable to uniform hashing, in which case another hash function should be specified.
Specify HASH0 to use the year and month values to rotate the data storage destination RDAREA every month.
Rebalancing table:
Specify one of the hash functions HASHA to HASHF.
HASHF is usually specified, because it provides the most uniform hashing. However, some partitioning key data is not amendable to uniform hashing, in which case another hash function should be specified.
HASH1, HASHA
This hash function can be used for column hash partitioning for all data types. It hashes by using all bytes* of the data in all the columns specified for partitioning. HASH1 can be specified for columns whose data length is at least 0 bytes.
HASH2, HASHB
This hash function can be used for column hash partitioning for all data types. It hashes by using all bytes* of the data in all the columns specified for partitioning. HASH2 can be specified for columns whose data length is at least 0 bytes.
HASH3, HASHC
This hash function can be used only when the data type of the columns specified for partitioning is INTEGER or SMALLINT. Hashing is performed using the last 2 bytes* of each partitioning column. HASH3 can be specified for columns whose data size is at least 2 bytes.
HASH4, HASHD
This hash function can be used only when the data type of the columns specified for partitioning is DATE. Hashing is performed using the first 4 bytes* of each partitioning column. HASH4 can be specified for columns whose data size is at least 4 bytes.
HASH5, HASHE
This hash function can be used only when the data type of the columns specified for partitioning is TIME. Hashing is performed using the first 3 bytes* of each partitioned column. HASH4 can be specified for columns whose data size is at least 3 bytes.
HASH6, HASHF
This hash function can be used for column hash partitioning for all data types. It is well suited for DECIMAL applications. It hashes by using all data bytes* in all the columns specified for partitioning. HASH6 can be specified for columns whose data length is at least 0 bytes.
HASH0
Specify this hash function to use the year and month values in the partitioning column to rotate and allocate the data storage destination RDAREA every month.
When this hash function is specified, the partitioning key must be a single column and its data type must be DATE, TIMESTAMP, CHAR(8), or CHAR(6). Use the date format 'YYYYMMDD' for CHAR(8), and use 'YYYYMM' for CHAR(6).
Use the following values for YYYY and MM:
YYYY: 0001-9999 (year)
MM: 01-12 (month)
* For the VARCHAR, MVARCHAR, or C type, hashing is performed by ignoring trailing spaces. If the sign portion is F in the DECIMAL, INTERVAL YEAR TO DAY, or INTERVAL HOUR TO SECOND type, hashing is performed by converting F to C.
25) reference-specification::= REFERENCES referenced-table [referential-constraint-operation-specification]

Specifies the referenced table to be referenced. When specifying a constraint operation, specify referential-constraint-operation-specification.

If reference-specification is specified, the table (referencing table) for which reference-specification is specified is subject to the operating rules shown in Table 3-30. The table (referenced-table) that is referenced by the table for which reference-specification is specified is subject to the operating rules shown in Tables 3-31 and 3-32.

Table 3-30 Operation on referencing tables with a reference-specification specification

Operation on foreign key constituent columnsRelationship between rows of a foreign key constituent column and rows of the referenced table referenced by the foreign keyResults
Add (INSERT)A row having a primary key constituent column value equal to the value of the foreign key constituent column for the row to be inserted exists in the referenced table.Y
A row having a primary key constituent column value equal to the value of the foreign key constituent column for the row to be inserted does not exist in the referenced table.The null value is found in the foreign key constituent column for the row to be inserted.Y
The null value is not found in the foreign key constituent column for the row to be inserted.N
Update (UPDATE)A row having a primary key constituent column value equal to the value of the updated foreign key constituent column exists in the referenced table.Y
A row having a primary key constituent column value equal to the value of the updated foreign key constituent column does not exist in the referenced table.The null value is found in the updated foreign key constituent column.Y
The null value is not found in the updated foreign key constituent column.N
Legend:
Y: Can be operated on the referencing table.
N: A constraint violation error occurs.
referenced-table:: = table-name
Specifies the name of the table to be referenced.
The following rules apply to table names:
  • Specify the name of the table that has the primary key.
  • The table name should be a base table.
  • The table name cannot be qualified with an RD node name.
  • The table owned by the user should be specified.
  • The table identifier being defined cannot be specified.
referential-constraint-operation-specification:: = {delete-operation [update-operation] | update-operation [delete-operation]}
Specifies an operation that is performed in synchronization with the updating or deletion of the primary key.
delete-operation:: = ON DELETE reference-operation
Specifies the operation to be performed when a row in the referenced table is deleted.
update-operation:: = ON UPDATE reference-operation
Specifies the operation to be performed when a row in the referenced table is updated.
reference-operation
CASCADE
Specify this operand when an operation on the primary key is to be communicated to a foreign key to maintain data integrity.
The following table shows operations that are allowed on referenced tables and the influence of those operations on the referencing table.

Table 3-31 Allowable operations on referenced tables with CASCADE specification, and the influence of those operations on the referencing table

Operations on the primary key constituent column of the referenced table referenced by the foreign key constituent columnRelationship between rows of a foreign key constituent column and rows of the referenced table referenced by the foreign keyImpact on referencing table
Delete (DELETE)A row having a foreign key constituent column value equal to the value of the primary key constituent column for the row to be deleted exists in the referencing table.Deletes row
A row having a foreign key constituent column value equal to the value of the primary key constituent column for the row to be deleted does not exist in the referencing table.No impact
Update (UPDATE)A row having a foreign key constituent column value equal to the value of the primary key constituent column for the pre-update row exists in the referencing table.Updates using a value equal to the primary key
A row having a foreign key constituent column value equal to the value of the primary key constituent column for the pre-update row does not exist in the referencing table.No impact
RESTRICT
Specify this operand to check whether or not operations on the primary key affect the foreign key and to restrict the operations so that data integrity can be maintained.
The following table shows allowable operations on the referenced table.

Table 3-32 Operations on the referenced table with RESTRICT specification

Operations on the primary key constituent column of the referenced table referenced by the foreign key constituent columnRelationship between rows of a foreign key constituent column and rows of the referenced table referenced by the foreign keyResults
Delete (DELETE)A row having a foreign key constituent column value equal to the value of the primary key constituent column for the row to be deleted exists in the referencing table.N
A row having a foreign key constituent column value equal to the value of the primary key constituent column for the row to be deleted does not exist in the referencing table.Y
Update (UPDATE)A row having a foreign key constituent column value equal to the value of the primary key constituent column for the pre-update row exists in the referencing table.N
A row having a foreign key constituent column value equal to the value of the primary key constituent column for the pre-update row does not exist in the referencing table.Y
Legend:
Y: Operation can be performed on the referenced table.
N: A restriction violation error occurs.
The default for the reference restriction operation itself is ON DELETE RESTRICT ON UPDATE RESTRICT.
The default for delete-operation is ON DELETE RESTRICT; the default for update-operation is ON UPDATE RESTRICT.
If CASCADE is specified for the referential constraint operation, HiRDB generates a trigger to perform restriction operations.
The following table shows the names of triggers that are created. All trigger names are 21 bytes in length.

Table 3-33 Names of triggers created by HiRDB

Referential constraint operationTrigger name
delete-operation(DRAyyyymmddhhmmssth)
update-operation(URAyyyymmddhhmmssth)
The SQL compile option for a trigger is the same as the default SQL compile option that is in effect in the trigger definition. For details about the default value, see CREATE TRIGGER (Define a trigger) in this chapter.
If more than one foreign key is specified, restrictions are performed in the following sequence:
  1. CASCADE
  2. RESTRICT
If CASCADE is specified more than once, CASCADE restriction is performed in the order in which CASCADE is specified in tables.
If RESTRICT is specified more than once, HiRDB determines the order in which restriction is performed so that an optimal restrict check can be performed, and RESTRICT is performed in that order.
26) constraint-name-definition::= CONSTRAINT constraint-name

Specify this operand when defining a constraint name for a specified constraint.

constraint-name
Duplicate constraint names cannot be specified in a given schema.
If constraint-name is omitted, HiRDB assigns a default constraint name.
The following table shows constraint names that HiRDB assigns by default:

Table 3-34 Default constraint names assigned by HiRDB

TypeConstraint nameNotes
Referential constraintSingle column check constraint definitionThe name of the column for which a constraint is specifiedNone
Multicolumn referential constraint definitionThe first column name specified in the foreign keyNone
Check constraintCK_table-number_yyyymmddhhmmssth30 characters, fixed (table number: 10 characters, time: 16 characters)
Note
yyyymmddhhmmssth is a time stamp at the time of constraint definition (containing information up to 1/100 second).
The table number is 10 characters long, right justified, and zero filled on the right.
Constraint names specified by users carry the potential for duplication. Therefore, constraint names in the above format should not be specified.
27) WITH PROGRAM

When defining a foreign key, specify this operand to disable the applicable function, procedure, or an SQL object for which a trigger is enabled. If a foreign key is not defined, any WITH PROGRAM specification is ignored. The following table shows objects that are disabled by this operand.

Table 3-35 Disabled objects

Version used to create objectObject description
Object typeDisabling condition
07-00 or laterFunction, procedure, and trigger objectsWhen the object contains an UPDATE or DELETE statement that uses a table specified in REFERENCES
Before 07-00When the object contains an SQL table that uses a table specified in REFERENCES

Common rules

  1. A maximum of 500 tables can be defined for a single RDAREA.
  2. Columns for which a cluster key is defined cannot be updated.
    However, if a column for which a cluster key is defined contains a variable-length character type column with a definition length of 256 bytes or greater, the column can be updated. If the cluster key is updated, the updated row can lose its clustering effect.
  3. The null value cannot be inserted into a table for which a cluster key is defined.
  4. Columns that belong to a cluster key cannot be updated.
  5. An RDAREA that is already assigned to a BLOB column or BLOB attribute cannot be specified as a LOB column storage RDAREA or LOB attribute storage RDAREA.
  6. The same columns cannot be specified as constituent columns in the CLUSTER KEY clause and the PRIMARY KEY clause. To define the same columns as cluster key and primary key constituents, specify the PRIMARY CLUSTER KEY clause. In this context, same columns means columns that satisfy all the following conditions:
    • The lists of column names specified in CLUSTER KEY and PRIMARY KEY clauses and number of specified columns are identical.
    • Either the ascending order/descending order specifications are all in agreement or they are all in reverse.
  7. RDAREAs using the inner replica facility and those not using the facility cannot be specified on a mixed basis in the table storage RDAREAs, LOB column storage RDAREAs, LOB attribute storage RDAREAs, or index storage RDAREAs. When specifying an RDAREA to which the inner replica facility is applied, specify the name of the original RDAREA.
  8. For execution conditions for CREATE TABLE using the inner replica facility, see the manual HiRDB Staticizer Option Version 7.
  9. HiRDB Dataextractor and HiRDB Datareplicator should not be used to affect data on the following tables:
    • Falsification-prevented tables
    • Tables containing columns for which NOT NULL-constrained and SYSTEM GENERATED are specified
  10. If a cluster key or the primary key is defined, the index identifier for the index being defined is determined according to the rules shown in Table 3-36:

    Table 3-36 Index identifier that is defined

    Specification itemIndex identifier
    CLUSTER(CLUSTER table number)
    PRIMARY(PRIMARY table number)
    PRIMARY CLUSTER(PRI-CLS table number)
    Note
    The table number part is a value consisting of 10 characters, right-justified and zero-filled on the left.
    A fixed value is specified as a total of 19 characters (of which 9 characters are the parentheses and the characters shown previously).
  11. A maximum of 30,000 columns can be specified per table.
    The sum of the column lengths (data lengths) must satisfy the formulas shown below.
    Table 3-37 shows the lengths (data lengths) of columns.
    • Table without FIX specification (table manipulation)

      [Figure]

    • Table with FIX specification (table definition)

      [Figure]

      Table 3-37 Predefined-type data lengths

      ClassificationData type and conditionData length
      (in bytes)
      Numeric dataINTEGER4
      SMALLINT2
      LARGE DECIMAL(m,n)1[Figure]m[Figure] 2 [Figure]
      + 12
      FLOAT or DOUBLE PRECISION8
      SMALLFLT or REAL4
      Character dataCHARACTER(n)n3
      VARCHAR(n)d[Figure] 255Elements of a repetition columnd + 2
      Other than the aboved + 1
      d[Figure] 2566
      VARCHAR(n)
      No-split option specified
      n[Figure] 255Attributes of abstract data typed + 3
      Elements of a repetition columnd + 2
      Other than the aboved + 1
      n[Figure] 2566
      National character dataNCHAR(n) or NATIONAL CHARACTER(n)2 [Figure] n4
      NVARCHAR(n)d[Figure] 127Elements of a repetition column2 [Figure] d + 2
      Other than the above2 [Figure] d + 1
      d[Figure] 1286
      NVARCHAR(n)
      No-split option specified
      n[Figure] 127Attributes of abstract data type2 [Figure] d + 3
      Elements of a repetition column2 [Figure] d + 2
      Other than the above2 [Figure] d + 1
      n[Figure] 1286
      Mixed character dataMCHAR(n)n3
      MVARCHAR(n)d[Figure] 255Elements of a repetition columnd + 2
      Other than the aboved + 1
      d[Figure] 2566
      MVARCHAR(n)
      No-split option specified
      n[Figure] 255Attributes of abstract data typed + 3
      Elements of a repetition columnd + 2
      Other than the aboved + 1
      n[Figure] 2566
      Date dataDATE4
      Time dataTIME3
      Time stamp dataTIMESTAMP(n)7 + (n[Figure] 2)
      Date interval dataINTERVAL YEAR TO DAY5
      Time interval dataINTERVAL HOUR TO SECOND4
      Large-object dataBLOB9
      Binary dataBINARY(n)n[Figure] 255d + 3
      n[Figure] 25615
Legend:
d: Actual data length (number of characters)
m, n: Positive integers
1 This is a fixed-point number with a total of m digits with n decimal places. The default for m is 15.
2 If SUPPRESS DECIMAL is specified as a table option during the definition of the table, the data length is ([Figure] k[Figure] 2 [Figure] + 2), where k denotes the number of effective digits at the time of data storage (the number of digits exclusive of the leading zeros). The SUPPRESS DECIMAL option should not be used in the following case, where a denotes the total value of data lengths of the columns in the table when either SUPPRESS DECIMAL or the column data suppress specification is not used:
32717 < (a + number-of-columns-in-table[Figure] 2 + 8)
3 If column data suppression is specified and data is suppressed, n is (n - b + 4). Data suppression is executed only if the last character of column data is a space and if there are 4 or more single-byte spaces that are contiguous with the last character, at the time of the column data suppress specification. b denotes the number of spaces that are contiguous with the last character in the column data.
If, however, column data suppression is specified and no data is suppressed, one byte of additional information is added per column.
The column data suppress specification should not be used in the following case, where a denotes the total value of the data lengths of the columns in the table when either SUPPRESS DECIMAL or the column data suppress specification is not used:
32717 < (a + number-of-columns-in-table[Figure] 2 + 8)
4 If column data suppression is specified and data is suppressed, 2[Figure] n is (2 [Figure] n - 2 [Figure] b + 5). Data suppression is executed only if the last character of column data is a space and if there are 3 or more double-byte spaces that are contiguous with the last character, at the time of the column data suppress specification. b denotes the number of spaces that are contiguous with the last character in the column data.
If, however, column data suppression is specified and no data is suppressed, one byte of additional information is added per column.
The column data suppress specification should not be used in the following case, where a denotes the total value of the data lengths of the columns in the table when either SUPPRESS DECIMAL or the column data suppress specification is not used:
32717 < (a + number-of-columns-in-table[Figure] 2 + 8)

Rules on referential constraints

  1. A given referenced table cannot be referenced from a foreign key for the same foreign key constituent column (even though it may not have the same sorting order).
  2. A foreign key cannot be specified for a table that was defined by specifying WITHOUT ROLLBACK.
  3. If the table defined by specifying WITHOUT ROLLBACK contains the primary key, foreign keys that reference the primary key cannot be defined.
  4. Foreign keys cannot be specified for a shared table.
  5. Foreign keys cannot be specified for a falsification-prevented table.
  6. A maximum of 255 foreign keys can be defined per table.
  7. A maximum of 255 foreign keys can be defined per primary key.

Rules on check constraints

  1. A maximum of 254 check constraints can be defined in a table. The maximum allowable sum of Boolean operators (except for AND and OR in WHEN search conditions of CASE expressions) specified in check constraints in a table and the number of check constraints is also 254.
  2. When specifying more than one condition, Hitachi recommends that check constraints be defined on the basis of separate conditions instead of grouping the conditions into a single check constraint. In this manner, if a constraint violation arises, the violating condition can easily be determined from a constraint name.
  3. A check constraint definition cannot be specified for a falsification-prevented table.
  4. If the BLOB type is specified in a search condition in a check constraint, or a BINARY type column with a definition length of 32,001 bytes or greater is specified, the following SQL statement cannot be executed:
    • Updating by concatenation operations in an UPDATE statement of the BLOB type or the BINARY type with a definition length of 32,001 bytes or greater, specified in the search condition in the check constraint.

Notes

  1. The CREATE TABLE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. PCTFREE=(0,0) must be specified to set 0% for both the table's unused space percentage and the percentage of free pages per segment.
  3. To pass or receive date data using CHAR(10) by means of the row-unit interface, the columns must be defined using CHAR(10) instead of the date data type.
  4. To pass or receive time data using CHAR(8) by means of the row-unit interface, the columns must be defined using CHAR(8) instead of the time data type.
  5. When passing or receiving time stamp data in CHAR in 19, 22, 24, or 26 bytes by using the by-row interface, specify columns in CHAR in 19, 22, 24, or 26 bytes without using the time stamp data type.
  6. When you define a falsification-prevented table, Hitachi recommends that only the falsification-prevented table is stored in the table storage RDAREA. If pdrorg terminates abnormally relative to the falsification-prevented table, the affected RDAREA cannot be released from the hold status until the reorganization process is complete. Consequently, if other tables and indexes are stored in the RDAREA storing the falsification-prevented table, those tables and indexes also become unavailable if pdrorg fails.
  7. If table definition changes are made for a referencing table specifying CASCADE as a reference operation, the trigger, generated by HiRDB, for performing constraint operations can be disabled in some cases. The trigger is disabled under the following conditions:
    • Condition under which the generated trigger is disabled when the referential constraint operation specification is ON UPDATE CASCADE
      [Figure] When table definitions are changed for a referencing table (changing SPLIT for the table or modifying the default for the table)
      [Figure]When an index is defined for a referencing table
      [Figure]When the index for a referencing table is deleted
      [Figure]When a trigger with a trigger timing UPDATE is defined for a referencing table
      [Figure]When a trigger with a trigger timing UPDATE defined for a referencing table is deleted
      [Figure]When a table definition is changed for the primary key constituent column of the referenced table referenced by a referencing table
    • Condition that disables the trigger that is generated when the referential constraint operation specification is ON DELETE CASCADE
      [Figure] When table definitions are changed for a referencing table (changing SPLIT for a column or modifying the default for a column)
      [Figure]When an index is defined for a referencing table
      [Figure]When the index for a referencing table is deleted
      [Figure]When a trigger with a trigger timing DELETE is defined for a referencing table
      [Figure]When a trigger with a trigger timing DELETE defined for a referencing table is deleted
      Any disabled trigger should be recreated using ALTER ROUTINE.
  8. If more than one referential constraint specifying ON UPDATE CASCADE as a reference operation is specified, the same table name should not be specified for the referenced table.
    However, the above rule does not apply if all of the following conditions are met:
    • The applicable multiple foreign key constituent columns with a reference specification are not duplicated.
    • Check constraints and referential constraints related to the applicable multiple foreign key constituent elements with a reference specification are not defined.

Examples

  1. Define a stock table (STOCK):

    CREATE TABLE STOCK
       (PCODE CHAR(4),PNAME NCHAR(8),
        COLOR NCHAR(1),PRICE INTEGER,SQTY INTEGER)

  2. Define a stock table (STOCK) with the following conditions:
    • Table is a FIX table
    • Table data is to be stored in a user RDAREA (RDA1)
    • Because the inventory table is a fixed-length table without a cluster key, the percentage of free area and the percentage of free pages per segment are both to be 0

    CREATE FIX TABLE STOCK
       (PCODE CHAR(4),PNAME NCHAR(8),
        COLOR NCHAR(1),PRICE INTEGER,SQTY INTEGER)
     IN RDA1
     PCTFREE=(0,0)

  3. Define a stock table (STOCK) with the following conditions:
    • The product code column (PCODE) is to be defined as a uniqueness-constrained cluster key
    • The table data and index are to be partitioned into three RDAREAs, each with the following storage conditions:
      Storage conditionsStorage RDAREAs
      Table dataIndex
      101M |PCODE |202MRDA1RDA4
      302S |PCODE |412MRDA2RDA5
      591L |PCODE |591SRDA3RDA6

    CREATE TABLE STOCK
      (PCODE CHAR(4)
          UNIQUE CLUSTER KEY ASC
          IN ((RDA4),(RDA5),(RDA6)),
      PNAME NCHAR(10),
      COLOR NCHAR(5),
      PRICE INTEGER,
      SQTY INTEGER)
      IN ((RDA1)PCODE<='202M',
        (RDA2)PCODE<='412M',
        (RDA3))

  4. Define a stock table (STOCK) with the following conditions:
    • The product name column (PNAME) and the color column (COLOR) are to be defined as a cluster key, and the index for the cluster key is to be sorted in ascending order of the product names and descending order of the colors
    • The table data is to be stored in a user RDAREA (RDA1)
    • The index is to be stored in a user RDAREA (RDA2)

    CREATE TABLE STOCK
      (PCODE CHAR(4),PNAME NCHAR(10),
       COLOR NCHAR(5),PRICE INTEGER,SQTY INTEGER)
    IN RDA1
    CLUSTER KEY (PNAME ASC,COLOR DESC) IN RDA2

  5. Define an employee table containing the abstract data type t_EMPLOYEE.

    CREATE TABLE STAFF_TABLE
      (EMPLOYEENO INTEGER,
       DOCUMENT_DATA_BLOB (6000) IN (LRDA1),(LRDA2))
       EMPLOYEE T_EMPLOYEE ALLOCATE(PHOTOGRAPH_OF_THE_FACE
          IN ((LRDA03),(LRDA04))
        ) IN ((RDA1) EMPLOYEE_NO<=700000,(RDA2))

  6. Define an order table (ORDER) under the following conditions:
    • The table is designated as a falsification-prevented table.
    • The deletion-prevented duration is 10 years.
    • An OINSDATE column is defined as an insert history maintenance column.

    CREATE TABLE ORDER
       (FNO CHAR(6),CCODE CHAR(5),PCODE CHAR(4),
        OQTY INTEGER,ODATE DATE,OTIME TIME,
        OINSDATE DATE NOT NULL
        WITH DEFAULT SYSTEM GENERATED)
    INSERT ONLY WHILE 10 YEARS BY OINSDATE

  7. Define an inventory table (STOCK) under the following conditions:
    • Store the table as a matrix-partitioned table on a partitioned basis in 6 user RDAREAs.
    • The following storage conditions apply:
      Storage conditionStoring RDAREA
      PCODE[Figure] 202M AND PRICE[Figure] 5000RDA1
      PCODE[Figure] 202M AND PRICE > 5000RDA2
      202M < PCODE[Figure] 412M AND PRICE[Figure] 5000RDA3
      202M < PCODE[Figure] 412M AND PRICE > 5000RDA4
      PCODE > 412M AND PRICE[Figure] 5000RDA5
      PCODE > 412M AND PRICE > 5000RDA6

    CREATE TABLE STOCK
       (PCODE CHAR(4) NOT NULL,
        PRICE INTEGER NOT NULL)
     PARTITIONED BY MULTIDIM (PCODE(('202M'),('412M')),
                              PRICE((5000)))
     IN ((RDA1,RDA2),(RDA3,RDA4),(RDA5,RDA6))

  8. Define an inventory table (STOCK) with the following conditions:
    • In the product code (PCODE) column, define a check constraint so that data cannot be inserted into the column or the column cannot be updated with a product with a size other than S, M, or L:

     CREATE TABLE STOCK
         (PCODE CHAR(4) CONSTRAINT CHECK_SIZE
         CHECK(PCODE LIKE '%S' OR PCODE LIKE '%M
         OR PCODE LIKE '%L'),
         PNAME NCHAR(8),
         COL NCHAR(1),PRICE INTEGER,SQTY INTEGER)

  9. Define a referential constraint for a single column:
    • If a row in a parts name table is deleted, also delete the corresponding row in the name table.
      Define a referenced table (parts name table (DEPT1)), and designate the parts code (DNO) column as the primary key:

     CREATE TABLE DEPT1
         (DNO CHAR(3) PRIMARY KEY,DNAME NVARCHAR(20),MGR CHAR(8))

    Define a referencing table (employee name table (EMP1)), and designate the parts code (DNO) column as a foreign key.

     CREATE TABLE EMP1
         (ENO CHAR(8),ENAME NVARCHAR(25),
          DNO CHAR(3)
              CONSTRAINT EMP1_K
              REFERENCES DEPT1 ON DELETE CASCADE)

    [Figure]

  10. Define a referential constraint for multiple columns.
    • If a row in a section name table is deleted and a row in the name table contains the same value as the section name table row being deleted, the following code suppresses the deletion of the section name table. Similarly, if a row in the section name table is to be updated and a row in the name table contains the same value as the section name table row being updated, the following code suppresses the updating of the section name table.
      Define a referenced table (section name table (DEPT2)), and designate the parts code (DNO) column and the section code (SNO) column as the primary key.

     CREATE TABLE DEPT2
         (DNO CHAR(3),SNO CHAR(3),
         SNAME CHAR(20),SHEAD CHAR(8),
         PRIMARY KEY(DNO,SNO))

    Define a referencing table (employee name table (EMP2)), and designate the parts code (DNO) column and the section code (SNO) column as foreign keys:

     CREATE TABLE EMP2
         (ENO CHAR(8),ENAME CHAR(25),
         DNO CHAR(3),SNO CHAR(3),
         CONSTRAINT EMP2_K
         FOREIGN KEY(DNO,SNO) REFERENCES DEPT2)

    [Figure]