- 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:
- The FIX option is incompatible with the following data types:
- VARCHAR
- NVARCHAR
- MVARCHAR
- BLOB
- Abstract data type
- A repetition column for which FIX has been specified cannot be specified.
- The FIX option can be specified only if the row length does not exceed the following value:
Page length of RDAREA in which rows are stored |1000![[Figure]](figure/zueng011.gif)
1000
- The NOT NULL option is assumed for all columns of a table for which the FIX option is defined.
- 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 specified | Index storage RDAREA specified* | Table storage RDAREA determination method |
---|
No | ![[Figure]](figure/zueng033.gif) | A table storage RDAREA is determined according to the following priority:
- Of the public RDAREAs to which the inner replica facility is not applied, the public RDAREA that has the fewest defined tables
- If there is more than one RDAREA meeting condition 1, the first RDAREA found by HiRDB
- 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
- If there is more than one RDAREA meeting condition 3, the first RDAREA found by HiRDB
|
Yes | No | A table storage RDAREA is determined according to the following priority:
- Of the public RDAREAs to which the inner replica facility is not applied, the public RDAREA that has the fewest defined tables
- If there is more than one RDAREA meeting condition 1, the public RDAREA having the fewest index definitions
- If there is more than one RDAREA meeting conditions 1 and 2, the first RDAREA found by HiRDB
- 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
- If there is more than one RDAREA meeting condition 4, the public RDAREA having the fewest index definitions
- 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 | - Of the public RDAREAs to which the inner replica facility is not applied, the public RDAREA that has the fewest defined tables
- If there is more than one RDAREA meeting condition 1, the first RDAREA found by HiRDB
|
RDAREA (original RDAREA) subject to inner replica facility | - Of the RDAREAs subject to the inner replica facility, the original and public RDAREAs having equal numbers of replica RDAREAs
- If there is more than one RDAREA meeting condition 1, the first RDAREA found by HiRDB
|
- Legend:
: 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:
- When multiple storage conditions are specified, the same column name must be specified in all of them.
- 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.
- 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.
- For data insertion, data cannot be inserted if there is no RDAREA corresponding to the data.
- For data updating, you cannot update column values that are specified in storage conditions.
- 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:
- Values in the column that is specified in column-name cannot be updated.
- The column specified in column-name should be a NOT NULL column (NOT NULL constrained, FIX specification, cluster key, or primary key).
- If a cluster key is specified for any column, boundary values cannot be specified for any other columns.
- If multiple columns constitute a cluster key, boundary values cannot be specified for any of those columns except the first column.
- 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:
- Any of 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 or character string literals with a length of 256 bytes or greater
Hexadecimal character string literals
- Boundary values must be specified in ascending order; they must all be distinct values.
- A maximum value should not be specified in the boundary value that is specified last.
- Specify table storage RDAREAs and boundary values alternately so that the specification begins and ends with a table storage RDAREA.
- The maximum number of table storage RDAREAs is 3,000.
- The same table storage RDAREA can be specified multiple times, provided that it is not specified twice in succession.
- 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:
- The specified column should be made NOT NULL. The following methods can be used to make a column NOT NULL:
Defining the column as a FIX table
Specifying NOT NULL in the column definition
Defining a cluster key or the primary key
- The values in the specified column cannot be updated.
- A repetition column cannot be specified in column-name.
- 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:
- Specify a literal in boundary-value.
- Specify boundary values in ascending order.
- Any of the following items cannot be specified in boundary-value:
Character string literals, national character string literals, or mixed character string literals with a length of 0
Character string literals and mixed or character string literals with a length of 256 bytes or greater
National character string literals with a length of 128 characters or greater
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:
- For rules on RDAREA names, see the explanation of RDAREAs under each operand.
- 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.
- The specifiable number of matrix-partitioned RDAREA lists is the number of boundary values specified in first-dimension boundary value list + 1.
- 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.
- The maximum number of RDAREAs that can be specified, exclusive of duplicates, is 1,024.
- The total number of specifiable RDAREAs is 3,000.
- 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.
- Cluster keys cannot be specified in a single column.
- 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:
- 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.
- The same column name cannot be specified more than once for multicolumn partitioning.
- For multicolumn partitioning, specify a combination of columns that have values that are not mutually dependent.
- A column specified in column-name should be a NOT NULL column (NOT NULL constrained, FIX specification, cluster key, or primary key).
- 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.
- For FIX hash-partitioning, the values in a column specified in column-name cannot be updated during data updating.
- A repetition column cannot be specified.
- 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:
- When a cluster key is defined, PCTFREE should be specified for the following purposes:
To create free space in the table so that data to be inserted after data initialization or reorganization can be clustered.
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.
- PCTFREE = (0,0) should be specified for a fixed-length table that does not have a cluster key.
- 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.
- 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]](figure/zu3s0060.gif)
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:
- If SUPPRESS is specified and DECIMAL is omitted, DECIMAL is assumed.
- 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.
- 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.
- 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:
- 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 operation | Operation on the table |
---|
Row insertion | Updating a column in a row | Row deletion |
---|
Updated value same as pre-update value | Updated value different from pre-update value |
---|
WITHOUT ROLLBACK-specified table with an index definition | The 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 definition | Row-locking is released upon completion of the update. After processing, the update cannot be rolled back. |
- This option is ignored during execution of the database load utility and the database reorganization utility.
- When this option is specified, the FIX option should also be specified for the table. This option is not specifiable if SHARE is specified.
- 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:
- The free space reusage facility has no effect on LOB columns, abstract data type columns of the LOB attribute, or indexes.
- 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 insertion | Specified value for deletion-prevented duration | Last day of deletion prevention | Deletion-allowed date |
---|
2002-03-01 | 1 year1 | 2003-02-28 | 2003-03-01 |
1995-03-01 | 1 year1 | 1996-02-29 | 1996-03-01 |
2002-02-28 | 1 month2 | 2002-03-27 | 2002-03-28 |
2002-05-01 | 1 day3 | 2002-05-01 | 2002-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 variable | System common definition |
---|
pd_constraint_name |
---|
Not specified | LEADING | TRAILING |
---|
PDCNSTRNTNAME | Not specified | Before | Before | After |
LEADING | Before | Before | Before |
TRAILING | After | After | After |
- 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:
- 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.
- 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.
- 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.
- 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:
- 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.
- When the data type is CHAR or MCHAR, four or more one-byte spaces are suppressed.
- When the data type is NCHAR, three or more two-byte spaces are suppressed.
- This option cannot be specified for a FIX table.
- Column data suppression cannot be specified if the data type is an abstract data type.
- This option can be specified only when the data type is CHAR, MCHAR, or NCHAR.
- Column data suppression cannot be specified for a repetition column.
- One byte of additional information is added to a column for which this option is specified even if data suppression does not occur.
- 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:
- 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.
- 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.
- Required LOB attribute storage RDAREAs must be created or added in advance with the database initialization utility or the database structure modification utility.
- 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:
- The DEFAULT clause and the WITH DEFAULT clause cannot be specified for the same item.
- 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.
- 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:
- If predefined-value is omitted, the predefined value for WITH DEFAULT is assumed.
- In predefined-value, a data type in which the effective upper digits are nullified during insertion cannot be specified.
- NULL as a predefined value cannot be specified in a NOT NULL constrained column.
- 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:
- NOT NULL constraint
- Single-column uniqueness constraint definition
- Single-column check constraint definition
- Single-column referential constraint definition
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:
- The attribute cannot be specified for columns for which SYSTEM GENERATED is specified.
- The attribute cannot be specified for any of the following non-updatable columns:
Cluster key constituent column
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 update | Column value after update | Updatability |
---|
Null value | Null value | Y |
Null value | Non-null value | Y |
Non-null value | Null value | N |
Non-null value | Non-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 type | UPDATE specification | UPDATE ONLY FROM NULL specification | No specification |
---|
Specifiable | Column value updatable | Specifiable | Column value updatable | Specifiable | Column value updatable |
---|
Non-falsification-prevented table | Y | Y | Y | Y | ![[Figure]](figure/zueng033.gif) | Y |
Falsification-prevented table | Y | Y | Y | Y* | ![[Figure]](figure/zueng033.gif) | N |
- Legend:
- Y: Updatable
- N: Not updatable
: 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:
- NOT NULL can be omitted when specifying WITH DEFAULT for a FIX table.
- WITH DEFAULT cannot be omitted if the data type is the abstract data type.
- 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 type | Column default value |
---|
INTEGER | 0 |
SMALLINT |
FLOAT |
SMALLFLT |
DECIMAL |
CHAR | Space |
NCHAR |
MCHAR |
VARCHAR | 1-byte space |
NVARCHAR | 1-character space |
MVARCHAR | 1-byte space |
DATE | Current date when a row is added |
TIME | Current time when a row is added |
TIMESTAMP | Current time stamp when a row is added |
INTERVAL YEAR TO DAY | 0 years, 0 months, 0 days |
INTERVAL HOUR TO SECOND | 0 hours, 0 minutes, 0 seconds |
BLOB | Data 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:
- None of the following data types can be specified for a column that composes a cluster key:
DECIMAL with a precision exceeding 19
BLOB
BINARY
Abstract data type
- A repetition column cannot be specified as a column that composes a cluster key.
- When a cluster key is specified, an index is defined for the specified column. A defined index cannot be deleted.
- The NOT NULL option is assumed for a cluster key column.
- Duplicated data cannot be inserted into a cluster key for which UNIQUE or PRIMARY is specified.
- Only one cluster key can be defined per table.
- When a cluster key is specified, the HASHA to HASHF hash functions cannot be specified.
- The total length of columns comprising a cluster key must satisfy the following formula:
Total length of columns
MIN((page size of index storage RDAREAs
2) - 1242, 4036)
- 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:
- Each specified RDAREA either must have already been created by the database initialization utility or must be added by the database structure modification utility.
- If no index storage RDAREA names are specified, the index is stored in the same RDAREAs that store the defined table.
- 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.
- 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.
- 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]](figure/zu3s0040.gif)
- Example 2: Partitioning by rows based on a boundary value
![[Figure]](figure/zu3s0050.gif)
- 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:
- The following data types cannot be specified for the columns that make up the primary key:
DECIMAL with a precision greater than 19 digits
BLOB
BINARY
Abstract data type
- A repetition column cannot be specified as a column comprising the primary key.
- Specifying a primary key causes an index to be defined for the specified columns. An index defined in this manner cannot be deleted.
- NOT NULL is assumed for a column that comprises the primary key.
- Duplicated data cannot be inserted into a primary key column.
- Only one primary key can be defined per table.
- For definability (UNIQUE specifiability) of row-partitioned tables, see Table 3-20.
- The total length of columns comprising the primary key must satisfy the following formula:
Total length of columns
MIN((page size of index storage RDAREAs
2) - 1242, 4036)
- 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:
- The RDAREA must be created in advance using the database initialization utility or added using the database structure modification utility.
- 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.
- 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.
- 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.
- 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:
- If a foreign key is specified for a column, the following data types cannot be specified on the column:
DECIMAL with a minimum precision of 20
BLOB
BINARY
Abstract data type
- A repetition column cannot be specified for a foreign key column.
- The foreign key and the primary key that references it must agree in all of the following items:
Corresponding data type
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:
- None of the following data types can be specified for the columns that comprise a cluster key:
DECIMAL with a precision exceeding 19
BLOB
BINARY
Abstract data type
- If a cluster key is comprised of multiple columns, the following data types, in addition to item 1, cannot be specified:
FLOAT
SMALLFLT
- A repetition column cannot be specified as a column that composes a cluster key.
- 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.
- The NOT NULL option is assumed for the columns that comprise a cluster key.
- Duplicated data cannot be inserted into a cluster key for which UNIQUE or PRIMARY is specified.
- Only one cluster key can be defined per table.
- The maximum number of columns comprising a cluster key for which PRIMARY is specified is 16.
- When a cluster key is specified, the HASHA to HASHF hash functions cannot be specified.
- The total length of columns comprising an index must satisfy the following formula:
Total length of columns
MIN((page size of index storage RDAREAs
2) - 1242, 4036)
- 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:
- Each specified RDAREA either must have been created by the database initialization utility or must be added by the database structure modification utility.
- If no index storage RDAREA names are specified, the index is stored in the same RDAREAs that store the defined table.
- 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.
- 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.
- 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:
- The following data types cannot be specified for the columns that make up the primary key:
DECIMAL with a precision greater than 19 digits
BLOB
BINARY
Abstract data type
- When the primary key is composed of multiple columns, the following data types, in addition to item 1, cannot be specified:
FLOAT
SMALLFLT
- A repetition column cannot be specified as a column comprising the primary key.
- 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.
- NOT NULL is assumed for a column that comprises the primary key.
- Duplicated data cannot be inserted into a primary key column.
- Only one primary key can be defined per table.
- The maximum number of columns comprising a cluster key for which PRIMARY is specified is 16.
- 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.
- The total length of columns comprising an index must satisfy the following formula:
Total length of columns
MIN((page size of index storage RDAREAs
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.
- 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:
- When a foreign key is specified, the following data types cannot be specified for columns that compose the foreign key:
DECIMAL with a minimum precision of 20
BLOB
BINARY
Abstract data type
- A repetition column cannot be specified for a column that composes a foreign key.
- The foreign key and the primary key that is referenced must be identical in all of the following items:
Corresponding data type
Corresponding data length
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:
- Multiple literals can be specified only when the = comparison operator is used.
- When multiple literals are specified, the same value cannot be specified more than once.
- 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.
19930025.
99981315.
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.
- 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
- The value specified in column-name cannot be updated.
- 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.
- If a cluster key is specified for a column, storage conditions cannot be specified for any other columns.
- The column specified in column-name must be NOT NULL (NOT NULL constraint, FIX specification, or a cluster key).
- When a multicolumn cluster key is specified, a storage condition cannot be specified for columns other than the leading column.
- 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 columns | Relationship between rows of a foreign key constituent column and rows of the referenced table referenced by the foreign key | Results |
---|
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 column | Relationship between rows of a foreign key constituent column and rows of the referenced table referenced by the foreign key | Impact 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 column | Relationship between rows of a foreign key constituent column and rows of the referenced table referenced by the foreign key | Results |
---|
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 operation | Trigger 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:
- CASCADE
- 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
Type | Constraint name | Notes |
---|
Referential constraint | Single column check constraint definition | The name of the column for which a constraint is specified | None |
Multicolumn referential constraint definition | The first column name specified in the foreign key | None |
Check constraint | CK_table-number_yyyymmddhhmmssth | 30 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 object | Object description |
---|
Object type | Disabling condition |
---|
07-00 or later | Function, procedure, and trigger objects | When the object contains an UPDATE or DELETE statement that uses a table specified in REFERENCES |
Before 07-00 | When the object contains an SQL table that uses a table specified in REFERENCES |