ALTER TABLE Syntax (Basic) | Teradata Vantage - ALTER TABLE Syntax (Basic) - Teradata Vantage

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 ]

ALTER TABLE Syntax (Basic) | Teradata Vantage - ALTER TABLE Syntax (Basic) - Teradata Vantage - Analytics Database

SQL Data Definition Language Syntax and Examples