![[Contents]](FIGURE/CONTENT.GIF)
Scalable Database Server, HiRDB Version 8 SQL Reference
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
![[Next]](FIGURE/AFTER.GIF)
ALTER TABLE (Alter table definition)
Function
ALTER TABLE has the following functions:
Privileges
Format
The item numbers in the following format correspond with the operand numbers:
| No. | Format |
|---|---|
1 |
ALTER TABLE [authorization-identifier.]table-identifier |
table-definition-change::={ column-addition-definition
|RDAREA-addition-definition
|column-attribute-change-definition
|column-deletion-definition
|table-name-change-definition
|column-name-change-definition
|partitioning-storage-condition-change-definition }
|
| No. | Format |
|---|---|
2 |
column-addition-definition::=
ADD column-name data-type[ARRAY [maximum-number-of-elements]] [NO SPLIT]
[[column-recovery-restriction-1]
{LOB-column-storage-RDAREA-specification
|matrix-partitioned-LOB-column-storage-RDAREA-specification
|abstract-data-type-definition-LOB-storage-RDAREA-specification
[plug-in specification]
|matrix-partitioned-LOB-attribute-storage-RDAREA-specification
[plug-in specification]}]
[DEFAULT clause]
{[NULL|NOT NULL [WITH DEFAULT]]1
|[[NOT NULL] WITH DEFAULT]2}
[updatable-column-attribute]
[WITH PROGRAM]
|
column-recovery-restriction-1::= RECOVERY[[ALL | PARTIAL | NO] |
|
LOB-column-storage-RDAREA-specification::=
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::= 2-dimension storage RDAREA specification matrix-partitioned-LOB-attribute-storage-RDAREA-specification::= 2-dimension storage RDAREA specification 2-dimension storage RDAREA specification::=(matrix-partitioning-RDAREA-list [, matrix-partitioning-RDAREA-list]...) matrix-partitioning-RDAREA-list::= (RDAREA-name[,RDAREA-name]...) |
|
abstract data type LOB 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)]...)}
[, 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)]...)}]...)
|
|
DEFAULT clause::= DEFAULT [default-value]
default-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}
|
|
updatable-column-attribute::= UPDATE [ONLY FROM NULL] |
|
3 |
RDAREA-addition-definition::=
ADD RDAREA table-storage-RDAREA-name
[FOR COLUMN column-name
{LOB-column-storage-RDAREA-specification
|abstract-date-type-definition-LOB-storage-RDAREA-specification}
[, column-name {LOB-column-storage-RDAREA-specification
|abstract-date-type-definition-LOB-storage-RDAREA-specification}]...]
[FOR INDEX index-identifier index-storage-RDAREA-specification
[, index-identifier index-storage-RDAREA-specification]...]
[FOR [PRIMARY] CLUSTER KEY index-storage-RDAREA-specification]
[FOR PRIMARY KEY index-storage-RDAREA-specification]
[WITH PROGRAM]
|
LOB-column-storage-RDAREA-specification::=
IN {LOB-column-storage-RDAREA-name
| (LOB-column-storage-RDAREA-name)
| ((LOB-column-storage-RDAREA-name)
[, (LOB-column-storage-RDAREA-name)]...)}
|
|
abstract data type LOB 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)]...)}
[, 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)]...)}]...)
|
|
index-storage-RDAREA-specification::=
IN {index-storage-RDAREA-name
| (index-storage-RDAREA-name)
| ((index-storage-RDAREA-name)
[, (index-storage-RDAREA-name)]...)}
|
|
4 |
column-attribute-change-definition::=
CHANGE{column-name{[{VARCHAR (data-length)
|NVARCHAR (data-length)
|MCHAR({*|data-length})
|MVARCHAR({*|data-length})}]
[ARRAY [{*|maximum-number-of-elements}]]
|[ARRAY [maximum-number-of-elements]]
|BINARY(data-length)}
[{NO SPLIT|SPLIT}]
[column-recovery-restriction-2]
[{SET DEFAULT clause|DROP DEFAULT}]
[WITH DEFAULT]
[updatable-column-attribute]
|CLUSTER KEY[UNIQUE]
|LOCK{ROW|PAGE}
|HASH hash-function-name
|SEGMENT REUSE {[number-of-segments[{K|M|G}]]|NO}}
| INSERT ONLY
[WHILE {date-interval-data| labeled-interval} BY column-name]}
[WITH PROGRAM]
|
column-recovery-restriction-2::= RECOVERY{ALL|PARTIAL|NO}
|
|
5 |
column-deletion-definition::= DROP column-name [WITH PROGRAM] |
6 |
table-name-change-definition::=
RENAME TABLE TO table-identifier
[WITH PROGRAM]
|
7 |
column-name-change-definition::=
RENAME COLUMN FROM pre-change-column-name TO post-change-column-name
[WITH PROGRAM]
|
8 |
partitioning-storage-condition-change-definition::=
CHANGE RDAREA row-partitioned-table-change-specification
[LOB-column-storage-RDAREA-change-specification]
[index-storage-RDAREA-change-specification
[index-storage-RDAREA-change-specification]...]
[cluster-key-storage-RDAREA-change-specification]
[primary-key-storage-RDAREA-change-specification]
[WITHOUT PURGE]
[WITH PROGRAM]
|
8-1 |
row-partitioned-table-change-specification::=
{[PARTITIONED] pre-change-boundary-value-list INTO post-change-boundary-value-partition-specification
|PARTITIONED CONDITION pre-change-RDAREA-information-list
INTO post-change-storage-condition-partition-specification}
|
8-2 |
pre-change-boundary-value-list::= boundary-value-list
boundary-value-list::= (({boundary-value | MAX}) [, ({boundary-value |MAX})]...)
|
8-3 |
post-change-boundary-value-partitioning-specification::= {table-storage-RDAREA-name
| (table-storage-RDAREA-name)
| ([(table-storage-RDAREA-name) boundary-value,]
...(table-storage-RDAREA-name))}
|
8-4 |
pre-change-RDAREA-information-list::=
{name-of-RDAREA-for-table
|(name-of-RDAREA-for-table)
|((name-of-RDAREA-for-table)
[,(name-of-RDAREA-for-table)]...[,OTHERS])
|OTHERS}
|
8-5 |
pre-change-RDAREA-information-list::=
{table-storage-RDAREA-name
|(table-storage-RDAREA-name)
|((table-storage-RDAREA-name)
[,(table-storage-RDAREA-name)]...[,OTHERS])
|OTHERS}
|
8-6 |
index-storage-RDAREA-change-specification::=
FOR INDEX index-name
INTO post-change-index-storage-RDAREA-name-list
post-change-index-storage-RDAREA-name-list::= RDAREA-name-list
index-name::=
{index-storage-RDAREA-name
| (index-storage-RDAREA-name)
| ((index-storage-RDAREA-name)
[, (index-storage-RDAREA-name)]...[,OTHERS])
|OTHERS}
|
8-7 |
primary-key-storage-RDAREA-change-specification::= FOR PRIMARY KEY INTO post-change-index-storage-RDAREA-name-list |
8-8 |
cluster-key-storage-RDAREA-change-specification::= FOR [PRIMARY] CLUSTER KEY INTO post-change-index-storage-RDAREA-name-list |
8-9 |
LOB-column-storage-RDAREA-change-specification::= FOR COLUMN column-name LOB-column-storage-RDAREA-change-list [, column-name-LOB-column-storage-RDAREA-change-list ]... |
8-10 |
LOB-column-storage-RDAREA-change-list::=
INTO {LOB-column-storage-RDAREA-name
| (LOB-column-storage-RDAREA-name)
| ((LOB-column-storage-RDAREA-name)
[, (LOB-column-storage-RDAREA-name)]...[,OTHERS])
|OTHERS}
|
Legend:
![[Figure]](FIGURE/ZUENG033.GIF)
: See specification locations 1 to 6.
1 Columns in a non-FIX table
2 Columns in a FIX table
Operands
![[Figure]](FIGURE/ZUENG030.GIF)
BLOB BINARY Abstract data type| Column value before change | Column value after change | Whether updatable |
|---|---|---|
| Null value | Null value | Y |
| Null value | Non-null-value | Y |
| Non-null-value | Null value | N |
| Non-null-value | Non-null-value* | N |
| 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 | |
Y |
| Falsification-prevented table | Y | Y | Y | Y* | |
N |
: Not applicable![[Figure]](FIGURE/ZUENG000.GIF)
(page-size-of-index-storage-RDAREA ![[Figure]](FIGURE/ZUENG002.GIF)
2) - 1242Table 3-6 Whether an index storage RDAREA can be specified depending on the index type
| Index definition method | Index-partitioning method | Specifiability of index storage RDAREA | |
|---|---|---|---|
| Index and primary key defined using CREATE INDEX format 1 | Row-partitioning on one server1 | On-server partitioning key index | Y |
| On-server non-partitioning key index | |||
| Row-partitioning only between servers2 | On-server partitioning key index | ||
| On-server non-partitioning key index | N5, 6 | ||
| Row partitioning on and between servers3 | On-server partitioning key index | Y | |
| On-server non-partitioning key index | |||
| Non-partitioning4 | Non-partitioning key index | N5 | |
| Index and cluster key defined using CREATE INDEX format 2 | Row-partitioning on one server1 | Y | |
| Row-partitioning only between servers2 | |||
| Row-partitioning on and between servers3 | |||
A column specified as an argument in a scalar function in a selection expression or a search condition A column specified as an argument in a function call in a selection expression or a search condition A column specified as an operation term for a concatenation operation in a selection expression or a search condition A column specified in a value expression in a CASE expression in a selection expression or a search condition A column specified in a value expression in a CAST specification in a selection expression or a search condition An item containing a row subquery An item containing a scalar subquery or a table subquery using set operations An item containing a table subquery with a results column count of 2 or greater in a FROM clause derived table or in a predicate other than the EXISTS predicate A selection expression containing a scalar subquery A row value constructor on the left side of a comparison predicate, IN predicate, or quantified predicate containing a scalar subquery A row value constructor, on the right side of a comparison predicate, containing a scalar subquery, with the number of row value constructor elements being 2 or greater A list of row value constructors, on the right side of an IN predicate, containing a scalar subquery Scalar subquery contained in a BETWEEN predicate, LIKE predicate, XLIKE predicate, or SIMILAR predicate 2) - 1242, 4036) Columns that are specified in a subquery in a comparison predicate, quantified predicate, or IN predicate Columns specified for duplicate elimination Columns specified for grouping or in a set function Columns specified in a selection expression with a query specification that is subject to set operations Columns that are specified during the definition of a view table that is expanded as an inner derived table in a query specification that satisfies one of the conditions for the creation of an inner derived table Columns for which UPDATE ONLY FROM NULL of the updatable column attribute is specified Columns to which UPDATE ONLY FROM NULL of the updatable column attribute is assigned at the same time as a change in the attribute's data length Cluster key constituent column Partitioning key constituent column (not including partitioning key constituent columns for a flexible hash partitioning table)| Column value before update | Column value after update | Updatable |
|---|---|---|
| Null value | Null value | Y |
| Null value | Non-null value | Y |
| Non-null value | Null value | N |
| Non-null value | Non-null value* | N |
| 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 | |
Y |
| Falsification-prevented table | N | Y | N | Y* | |
N |
: Not applicableThis option is specified when renaming a table.
A table that becomes the base for a view table A table specified in a CREATE PROCEDURE SQL procedure statement A table specified in a CREATE TRIGGER SQL procedure statement and a table that defines a triggerThis option is specified to change the name of a column.
Columns in a table that becomes the base for a view table Columns in a table specified in a CREATE PROCEDURE SQL procedure statement Columns in a table specified in a CREATE TRIGGER SQL procedure statement, trigger event columns, and columns specified in a trigger action condition| Table type | Table partitioning method | Execution type | ||
|---|---|---|---|---|
| Partitioning | Combining | |||
| Row-partitioned table | Key range partitioning | Boundary value specification | Partitions an arbitrarily selected boundary value into 2 to 16 values. | Combines 2 to 16 arbitrarily selected boundary values into a single value. |
| Storage condition specification | Partitions an arbitrarily selected RDAREA into 2 to 16 segments. | Combines 2 to 16 arbitrarily selected RDAREAs into a single RDAREA. | ||
| Table type | Partitioning method | Modifiability | ||
|---|---|---|---|---|
| Row-partitioned table | Key range partitioning | Storage condition specification | Table in which only = is used for the storage condition comparison operator | Y |
| Table in which an operator other than = is used for the storage condition comparison operator | N | |||
| Boundary value specification | Y | |||
| Hash partitioning | N | |||
| Matrix-partitioned table | First dimension: Boundary value specification Second dimension: Boundary value specification |
N | ||
| First dimension: Boundary value specification Second dimension: Hash partitioning |
N | |||
| Non-partitioned table* | |
N | ||
: Not applicable An index key, primary key, or cluster key is defined in the table that is to be modified. There is only a single table storage RDAREA in the table that is to be modified or in the table resulting from the modification. RDAREAs storing tables, indexes, primary keys, cluster keys, and LOB columns must all have the same generation number. If a referential constraint is defined for a table, RDAREAs that store the referencing table and the referenced table must all have the same generation number.Table 3-7 Dictionary tables from which check pending status is released when USE is specified for the pd_check_pending operand in the system definition
| Constraint defined in the modification-target table | Is there an RDAREA in which the check pending status is specified for the table information?# | Dictionary tables from which the check pending status is released | |
|---|---|---|---|
| Referential constraint | No | SQL_REFERENTIAL_CONSTRAINTS table | CHECK_PEND column |
| SQL_TABLES table | CHECK_PEND column | ||
| Check constraint | No | SQL_CHECKS table | CHECK_PEND2 column |
| SQL_TABLES table | CHECK_PEND2 column | ||
| Referential constraint and check constraint | Referential constraint: No Check constraint: No |
SQL_REFERENTIAL_CONSTRAINTS table | CHECK_PEND column |
| SQL_CHECKS table | CHECK_PEND2 column | ||
| SQL_TABLES table | CHECK_PEND column, CHECK_PEND2 column | ||
| Referential constraint: No Check constraint: Yes |
SQL_REFERENTIAL_CONSTRAINTS table | CHECK_PEND column | |
| SQL_TABLES table | CHECK_PEND column | ||
| Referential constraint: Yes Check constraint: No |
SQL_CHECKS table | CHECK_PEND2 column | |
| SQL_TABLES table | CHECK_PEND2 column | ||
| No constraint defined | No | |
|
: Not applicable Table, index, primary key, cluster key, and LOB column data of the RDAREA identified by the boundary value specified in the pre-modification boundary value list Data of the boundary value other than the modification target when the RDAREA identified by the boundary value specified in the pre-modification boundary value list is the same as the RDAREA that stores the data of the boundary value other than the modification target When the inner replica facility is used, table, index, primary key, cluster key, and LOB column data of all generations of replica RDAREAs of the RDAREA identified by the boundary value specified in the pre-modification boundary value list Table, index, primary key, cluster key, and LOB column data of the RDAREA specified in the pre-modification RDAREA information list When the inner replica facility is used, table, index, primary key, cluster key, and LOB column data of all generations of replica RDAREAs of the RDAREA identified in the pre-modification RDAREA information list| Item | Row-partitioned table with a boundary value specification | |
|---|---|---|
| Modified RDAREA specified in ALTER TABLE* | Duplicate | Duplication acceptable. |
| Contiguous | Contiguous allocation should be avoided. | |
| RDAREA of an entire table of the results of modification of a partitioning storage condition | Duplicate | Duplication acceptable. |
| Contiguous | During partitioning, the system consolidates contiguous RDAREAs into one area. | |
| Cannot be specified for combining. | ||
The number of table partitions is 1.|(table-storage-RDAREA-name)
Common rules
Notes
The defined trigger is later than the nullified trigger. The nullified trigger is the same as the defined table. The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification). The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event. The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event. The defined trigger is later than the nullified trigger. The nullified trigger is the same as the defined table. The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification). The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event. The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event. The defined trigger is later than the nullified trigger. The nullified trigger is the same as the defined table. The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification). The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event. The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event.Examples
ALTER TABLE STOCK
ADD PADRS VARCHAR(40)ALTER TABLE STOCK
ADD PADRS VARCHAR(40)
NOT NULL WITH DEFAULTALTER TABLE STOCK
CHANGE PADRS VARCHAR(60)ALTER TABLE STOCK
CHANGE CLUSTER KEY UNIQUEALTER TABLE STOCK
DROP PADRSALTER TABLE STOCK
DROP PADRS WITH PROGRAMALTER TABLE STOCK
ADD PMAP BLOB(1M) IN (RMAPLOB1)ALTER TABLE NSTOCK
ADD RDAREA RDA3
FOR COLUMN PMAP IN (RMAPLOB3)
FOR INDEX IPCODE IN (RDA4)
CREATE FIX TABLE "T1"("C1" INT,"C2" INT) PARTITIONED BY "C1"
IN(("TA1")100,("TA2")200,("TA3")400,("TA4")500,("TA5")600,("TA6"))
CREATE INDEX "I1" ON "T1"("C1")
IN(("IA1"),("IA2"),("IA3"),("IA4"),("IA5"),("IA6"))![[Figure]](FIGURE/ZU3S0200.GIF)
ALTER TABLE "T1" CHANGE RDAREA ((100),(200)) INTO "TA11" FOR INDEX "I1" INTO "IA11"
![[Figure]](FIGURE/ZU3S0210.GIF)
ALTER TABLE "T1" CHANGE RDAREA
((400)) INTO (("TA12")300,("TA13"))
FOR INDEX "I1" INTO (("IA12"),("IA13"))![[Figure]](FIGURE/ZU3S0220.GIF)
ALTER TABLE "T1" CHANGE RDAREA ((600),(MAX)) INTO "TA11" FOR INDEX "I1" INTO "IA11"
![[Figure]](FIGURE/ZU3S0230.GIF)
CREATE FIX TABLE "T1"("C1" INT,"C2" INT) PARTITIONED BY "C1"
IN(("TA1")100,("TA2")200,("TA3")400,
("TA4")500,("TA5")600,("TA6"))
CREATE INDEX "I1" ON "T1"("C1")
IN(("IA1"),("IA2"),("IA3"),
("IA4"),("IA5"),("IA6"))
![[Figure]](FIGURE/ZU3S0240.GIF)
ALTER TABLE "T1" CHANGE RDAREA
((400)) INTO (("TA3")300,("TA4"))
FOR INDEX "I1" INTO (("IA3"),("IA4"))![[Figure]](FIGURE/ZU3S0250.GIF)
CREATE FIX TABLE "T1"("C1" CHAR(3),"C2" INT)
IN(("TA1")"C1"='001',("TA2")"C1"='002',
("TA3")"C1"='003',("TA4")"C1"=('004','005'),("TA5"))
CREATE INDEX "I1" ON "T1"("C1")
IN(("IA1"),("IA2"),("IA3"),("IA4"),("IA5"))![[Figure]](FIGURE/ZU3S0400.GIF)
![[Figure]](FIGURE/ZUENG013.GIF)
State 2>>
ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
(("TA5")) INTO (("TA6")"C1"='006',("TA5"))
FOR INDEX "I1" INTO (("IA6"),("IA5"))![[Figure]](FIGURE/ZU3S0410.GIF)
State 3>>
ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
(("TA1"),("TA5")) INTO "TA5"
FOR INDEX "I1" INTO "IA5"![[Figure]](FIGURE/ZU3S0420.GIF)
State 4>>
ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
(("TA4")) INTO (("TA4")"C1"='004',("TA7")"C1"='005')
FOR INDEX "I1" INTO (("IA4"),("IA7"))![[Figure]](FIGURE/ZU3S0430.GIF)
State 5>>
ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
(("TA1"),("TA2")) INTO "TA2"
FOR INDEX "I1" INTO "IA2"![[Figure]](FIGURE/ZU3S0440.GIF)
CREATE TABLE ORDER
(FNO CHAR(6),TCODE CHAR(5),PCODE CHAR(4),
UACOL CHAR(60) UPDATE,
OQTY INTEGER,ODATE DATE,OTIME TIME,
OINSDATE DATE NOT NULL WITH DEFAULT SYSTEM GENERATED)
ALTER TABLE ORDER
CHANGE INSERT ONLY WHILE 10 YEARS BY OINSDATEAll Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.