CREATE TABLE Syntax | VantageCloud Lake - CREATE TABLE Syntax - Teradata VantageCloud Lake

CREATE table_kind TABLE table_specification 
  [ , table_option [,...] ] ( column_partition_definition )
  [ index [,...] ]
  [ table_preservation ][;]

table_kind

[ SET | MULTISET ] [ GLOBAL TEMPORARY | VOLATILE ]

table_specification

[ database_name. | user_name. ] table_name

table_option

{ MAP = map_name [COLOCATE USING colocation_name | 
  [NO] FALLBACK [PROTECTION] | 
  WITH JOURNAL TABLE = table_specification | 
  [NO] LOG | 
  [ NO | DUAL ] [BEFORE] JOURNAL | 
  [ NO | DUAL | LOCAL | NOT LOCAL ] AFTER JOURNAL | 
  CHECKSUM = { DEFAULT | ON | OFF } | 
  FREESPACE = integer [PERCENT] |
  mergeblockratio |
  datablocksize |
  blockcompression |
  
  [NO] AUTODBA [WITH CHECK]
}

column_partition_definition

{ column_name data_type [ column_data_type_attribute [,...] ] |

 [ COLUMN | ROW ] ( column_name data_type [column_attributes] [,...] )
   [ [NO] AUTO COMPRESS] |

 PERIOD FOR period_name ( period_begin_column , period_end_column ) |

 normalize_option |

 table_constraint ][,...]
}

index

[UNIQUE] PRIMARY INDEX [index_name] ( index_column_name [,...] ) | 
NO PRIMARY INDEX |
PRIMARY AMP [INDEX] [index_name] ( index_column_name [,...] ) |
PARTITION BY { partitioning_level | ( partitioning_level [,...] ) } |
UNIQUE INDEX [ index_name ] [ ( index_column_name [,...] ) ] [loading] |
INDEX [index_name] [ALL] ( index_column_name [,...] ) [ordering] [loading] 

table_preservation

ON COMMIT { DELETE | PRESERVE } ROWS

mergeblockratio

{ DEFAULT MERGEBLOCKRATIO | 
  MERGEBLOCKRATIO = integer [PERCENT] | 
  NO MERGEBLOCKRATIO
}

datablocksize

DATABLOCKSIZE = {
  data_block_size [ BYTES | KBYTES | KILOBYTES ] |
  { MINIMUM | MAXIMUM | DEFAULT } DATABLOCKSIZE
}

blockcompression

BLOCKCOMPRESSION = { AUTOTEMP | MANUAL | ALWAYS | NEVER | DEFAULT }
   [, BLOCKCOMPRESSIONALGORITHM = { ZLIB | ELZS_H | DEFAULT } ]
   [, BLOCKCOMPRESSIONLEVEL = { value | DEFAULT } ]

column_data_type_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 |
  compression_attribute |
  column_constraint_attribute |
  identity_column 
}

normalize_option

 NORMALIZE [ ALL BUT (normalize_ignore_column_name [,...]) ] 
   ON normalize_column
   [ ON { MEETS OR OVERLAPS | OVERLAPS [OR MEETS]} ]

table_constraint

CONSTRAINT constraint_name 
  { { UNIQUE | PRIMARY KEY } (column_name [,...]) |
      CHECK (boolean_condition) |
      FOREIGN KEY (referencing_column [,...]) references }

partitioning_level

  { partitioning_expression |
    COLUMN [ [NO] AUTO COMPRESS |
    COLUMN [ [NO] AUTO COMPRESS ] [ ALL BUT ] column_partition ]
  } [ ADD constant ]

loading

WITH [NO] LOAD IDENTITY

ordering

ORDER BY [ VALUES | HASH ] [ ( order_column_name ) ]

compression_attribute

{ NO COMPRESS |

  COMPRESS [ constant | ( { constant | NULL } [,...] ) ] |

  COMPRESS USING compress_UDF_name DECOMPRESS
    USING decompress_UDF_name
}

column_constraint_attribute

[ CONSTRAINT constraint_name ] 
  { UNIQUE | PRIMARY KEY | CHECK ( boolean_condition ) | references } |
  [ row_level_security_constraint_column_name [,...] ] CONSTRAINT

identity_column

GENERATE {ALWAYS | BY DEFAULT} AS IDENTITY
   [ ( START WITH constant |
       INCREMENT BY constant |
       MINVALUE constant‭	|‬
       NO MINVALUE |	
       MAXVALUE constant |
       NO MAXVALUE |
       [ NO ] CYCLE ) ]

references

 REFERENCES [ WITH [NO] CHECK OPTION ] referenced_table_name
   [ ( referenced_column_name [,...] ) ]

column_partition

 ( [ COLUMN | ROW ] { column_name | ( column_name [,...] ) }
    [[NO] AUTO COMPRESS]
  ) [,...]