17.10 - ALTER TABLE構文(基本) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1144-171K-JPN
Language
日本語 (日本)
ALTER TABLE [ database_name. | user_name. ] table_name
  { alter_option [,...] |
    table_option [,...] [ alter_option [,...] ] |
    normalize |
    DROP NORMALIZE |
    modify_primary |
    MODIFY NO PRIMARY [ INDEX ] [alter_partitioning ] |
    MODIFY alter_partitioning |
    FROM TIME ZONE = [ sign ] 'quotestring'
      [, TIMEDATEWZCONTROL = n ] [, WITH TIME ZONE ] |
    { SET | RESET } DOWN
  } [;]
alter_option
{ ADD add_option |
  MODIFY [ [ CONSTRAINT ] name ] CHECK ( boolean_condition ) |
  RENAME { column_name { AS | TO } column_name |
           constraint_name { AS | TO } constraint_name
         } |
  DROP drop_option
}
table_option
{ [NO] FALLBACK [ PROTECTION ] |
  WITH JOURNAL TABLE = [ database_name. ] table_name |
  [ NO | DUAL ] [ BEFORE ] JOURNAL |
  ON COMMIT { DELETE | PRESERVE } ROWS |
  [NO] LOG |
  [ NO | DUAL | [NOT] LOCAL ] AFTER JOURNAL |
  CHECKSUM = { DEFAULT | ON | OFF } [ IMMEDIATE ] |
  DEFAULT FREESPACE |
  FREESPACE = integer [ PERCENT ] |
  { DEFAULT | NO } MERGEBLOCKRATIO |
  MERGEBLOCKRATIO = integer [ PERCENT ] |
  
  { DATABLOCKSIZE = data_block_size [ BYTES | KBYTES | KILOBYTES ] |
    { MINIMUM | MAXIMUM | DEFAULT } DATABLOCKSIZE
  } IMMEDIATE |
  
  blockcompression |
  WITH [NO] [ CONCURRENT ] ISOLATED LOADING [ FOR { ALL | INSERT | NONE } ]

  [ USING FAST MODE { ON | OFF } ]
}
normalize
ADD NORMALIZE [ ALL BUT ( column_name [,...] ) ]
  ON column_name [ ON { MEETS OR OVERLAPS | OVERLAPS OR MEETS } ]
modify_primary
MODIFY [ [NOT] UNIQUE ] PRIMARY [ AMP ] [ INDEX ]
  [ index_name | NOT NAMED ] [ ( column_name [,...] ) ] [ alter_partitioning ]
alter_partitioning
{ { PARTITION BY { partitioning_level | ( partitioning_level [,...] ) } |  
    { DROP range_expression [ ADD range_expression ] |
      ADD range_expression
    } [,...] [ WITH { INSERT [ INTO ] save_table | DELETE } ] |

  NOT PARTITIONED
}
add_option
{ { column_specification | ( column_specification [,...] ) } [ INTO column_name ] |

  [ COLUMN | ROW | SYSTEM ] ( { column_name | column_specification [,...] } )
    [ [NO] AUTO COMPRESS ] |
  
  ( column_name ) [NO] AUTO COMPRESS |
  
  PERIOD FOR period_name ( period_begin , period_end ) |
  
  [ CONSTRAINT name ]
    { FOREIGN KEY ( column_name [,...] ) references |
      { UNIQUE | PRIMARY KEY } ( column_name [,...] ) |
	   CHECK ( boolean_condition ) |
    } |
	
  row_level_security_constraint_column_name [,...] CONSTRAINT
}
drop_option
{ PERIOD FOR period_name |
  name [ IDENTITY ] |
  CONSTRAINT name |
  [ CONSTRAINT name ] FOREIGN KEY ( column_name [,...] ) references |
  [ [ CONSTRAINT ] name ] CHECK |
  INCONSISTENT REFERENCES |
  row_level_security_constraint_column_name [,...] CONSTRAINT
}
blockcompression
BLOCKCOMPRESSION = { AUTOTEMP | MANUAL | ALWAYS | NEVER | DEFAULT }
  [, BLOCKCOMPRESSIONALGORITHM = { ZLIB | ELZS_H | DEFAULT } ]
  [, BLOCKCOMPRESSIONLEVEL = { value | DEFAULT } ]
partitioning_level
{ partitioning_expression |
  COLUMN [ [NO] AUTO COMPRESS ] [ [ ALL BUT ] ( column_partition [,...] ) ]
} [ ADD constant ]
range_expression
{ RANGE | RANGE#Ln }
  { BETWEEN range [,...] [, { NO RANGE [ { OR | , } UNKNOWN ] | UNKNOWN } ] |
    NO RANGE [ { OR | , } UNKNOWN ] |
    UNKNOWN |
    WHERE conditional_expression
  }
column_specification
column_name data_type [ column_attribute [...] ]
references
REFERENCES [ WITH [NO] CHECK OPTION ] referenced_table_name
  [ ( referenced_column_name [,...] ) ]
column_partition
[ COLUMN | ROW ] { column_name | ( column_name [,...] ) }
  [ [NO] AUTO COMPRESS ]
range
start_expression [ AND end_expression ] [ EACH range_size ]
data_type
{ INTEGER | SMALLINT | BIGINT | BYTEINT | DATE |

  { TIME | TIMESTAMP } [( fractional_seconds_precision)] [WITH TIME ZONE] |

  INTERVAL YEAR [( precision)] [TO MONTH] |

  INTERVAL MONTH [( precision)] |

  INTERVAL DAY [( precision)]
    [TO { HOUR | MINUTE | SECOND [(fractional_seconds_precision)] } ] |

  INTERVAL HOUR [(precision)]
    [TO { MINUTE | SECOND [(fractional_seconds_precision)] } ] |

  INTERVAL MINUTE [(precision)] [ TO SECOND [(fractional_seconds_precision)] ] |

  INTERVAL SECOND [(precision) [, fractional_seconds_precision]) |

  PERIOD (DATE) |

  PERIOD ({ TIME | TIMESTAMP } [(precision)] [ WITH TIME ZONE ]) |

  REAL |

  DOUBLE PRECISION |

  FLOAT [(integer)] |

  NUMBER [({ integer | * } [, integer ]...)] |

  { DECIMAL | NUMERIC } [(integer [, integer ]...)] |

  { CHAR | BYTE | GRAPHIC } [(integer)] |

  { VARCHAR | CHAR VARYING | VARBYTE | VARGRAPHIC } [(integer)] |

  LONG VARCHAR |

  LONG VARGRAPHIC |

  { BINARY LARGE OBJECT | BLOB | CHARACTER LARGE OBJECT | CLOB } (integer [ G | K | M ]) |

  [SYSUDTLIB.] { XML | XMLTYPE } [(integer [ G | K | M ])]
    [ INLINE LENGTH integer ] |

  [SYSUDTLIB.] JSON [(integer [ K | M ])] [ INLINE LENGTH integer ]
    [ CHARACTER SET { UNICODE | LATIN } | STORAGE FORMAT { BSON | UBJSON } ] |

  [SYSUDTLIB.] ST_GEOMETRY [(integer [ K | M ])] [ INLINE LENGTH integer ] |

  [SYSUDTLIB.] DATASET [(integer [ K | M ])] [ INLINE LENGTH integer ]
    storage_format |

  [SYSUDTLIB.] { UDT_name | MBR | ARRAY_name | VARRAY_name }
}
column_attribute
{ { UPPERCASE | UC } |
  [NOT] { CASESPECIFIC | CS } |
  FORMAT quotestring |
  TITLE quotestring |
  NAMED name |
  DEFAULT { number | USER | DATE | TIME | NULL } |
  WITH DEFAULT |
  CHARACTER SET server_character_set |
  [NOT] NULL |
  [NOT] AUTO COLUMN |
  NO COMPRESS |
  COMPRESS [ constant | ( { constant | NULL } [,...] ) ] |
  COMPRESS USING compress_UDF_name DECOMPRESS USING decompress_UDF_name |
  [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY | CHECK ( boolean_condition ) | references }
}
storage_format
STORAGE FORMAT { Avro | CSV [ CHARACTER SET { UNICODE | LATIN } ] }
    [ WITH SCHEMA [ database. ] schema_name ]