43. CREATE TABLE in Teradata

CREATE TABLE is DDL command which is used to create teradata tables.

When executed, the CREATE TABLE statement creates and stores the table structure definition in the Teradata Data Dictionary.

The CREATE TABLE statement includes:

·

Create Table options

·

Column definitions

·

Table-level constraints

·

Index definitions

Syntax :

CREATE <SET/MULTISET> TABLE employee
<Create Table Options>
<Column Definitions>
<Table-level Constraints>
<Index Definitions>;
Where,

SET  - No duplicate rows allowed

MULTISET -duplicate rows allowed

Table options are,
Fallback  - copy of data is maintained by another AMP
No Fallback  - No copy of data.
Journaling - Keep single/dual copy of row BEFORE/AFTER updates to the row
Freespace - % of freespace (0-75%) that will remain on a cylinder during subsequent loading operations
Datablocksize - Size of data block in which row(s) of the table are stored

Column definitions

Column name - Name the column
Data type – Declare the column to be a character, byte,numeric, or graphic data type.

Data type Attributes are  Specify DEFAULT, FORMAT, TITLE, NULL,CASESPECIFIC, UPPERCASE.

Column Storage Attributes – Compress NULL values or a specified value.

Column-level Constraint Attributes – Specify the single column as a primary key or foreign key.
– Specify the single column as unique (must be NOT NULL).
– Specify constraint conditions on the column
Example :

CREATE SET TABLE emp ,NO FALLBACK ,
     (
      EmpNo SMALLINT FORMAT '999' NOT NULL,
      Name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      Salary DECIMAL(8,2),
      City VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
      State VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      doj DATE FORMAT 'YYYY-MM-DD')
UNIQUE PRIMARY INDEX ( EmpNo );