CREATE TABLE Syntax | Teradata Vantage - 17.10 - CREATE TABLE Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)
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 |
  isolated_loading
}
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 } ]
isolated_loading
WITH [NO] [CONCURRENT] ISOLATED LOADING [ FOR { ALL | INSERT | NONE } ]
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]
  ) [,...]