Creating a table in Teradata is almost similar to other Relational Databases. CREATE TABLE or CT is the command is used to create a table in Teradata.
CREATE TABLE Teradata Syntax:
CREATE SET/MULTISET TABLE database_name.table_name, [NO] FALLBACK (column_name data_type attribute, column_name data_type attribute, column_name data_type attribute) [UNIQUE][NO] PRIMARY INDEX (primary_index_column);
SET/ MULTISET syntax is used to define the uniqueness of the records in the table. The SET table does not allow any duplicate record wherein the MULTISET table allow duplicate records. If you do not specify anything Teradata by default creates SET.
FALLBACK defines duplicate copy of the table will be maintained in case of any failure. Default is the NO fallback. Define FALLBACK only on the critical tables in the database as it consumes extra space.
Database_Name: Where the table will be created.
Table Name: The name of the table.
Column_name: Name of the column you want in the table.
Data_type: Specifies single datatype for a column.
Attribute: Defines attributes for a column. Example – NOT NULL, NOT CASE SPECIFIC, UPPER CASE, FORMAT, etc.
[UNIQUE/NO] PRIMARY INDEX: You have to define at least one primary index in Teradata that can unique or non-unique in nature. If you don’t want any primary index in your table there is an option called NO PRIMARY INDEX in Teradata.
The primary index column defines the column(s) that will work as a primary index in Teradata. Column or combination of columns can be unique or non-unique.
CREATE TABLE Teradata Example:
The following example creates a set table called Employee in Employee_database with the FALLBACK option.
CREATE SET TABLE Employee_Database.Employee, FALLBACK (Employee_Id INTEGER, Employee_Name CHAR(25), Salary DECIMAL(8,2), DOB DATE FORMAT 'YYYY-MM-DD', Job_Title VARCHAR(25), Dept_No SMALLINT, Marital_Status CHAR, No_Of_Dependents BYTEINT) UNIQUE PRIMARY INDEX (Employee_Id);
Once the table is created you can do the SHOW TABLE command to see the table definition in Teradata.
show table Employee_Database.Employee; CREATE SET TABLE Employee_Database.Employee ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( Employee_Id INTEGER, Employee_Name CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC, Salary DECIMAL(8,2), DOB DATE FORMAT 'YYYY-MM-DD', Job_Title VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC, Dept_No SMALLINT, Marital_Status CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC, No_Of_Dependents BYTEINT) UNIQUE PRIMARY INDEX ( Employee_Id );
Other versions of creating tables in Teradata:
By now you have learned how to create a table in Teradata. There are other various methods by which you can get your desired table quickly.
Teradata create table as select:
The below example shows to create an employee table based on their marital status. Suppose you want only those employees whose marital status is SINGLE. This can be easily done using our existing table.
CREATE TABLE Employee_Database.bachelor_employee AS ( SELECT * FROM Employee_Database.Employee e WHERE e.Marital_Status = 'S' ) WITH DATA;
Teradata create table from another table with full data:
The below example will just create another copy of the employee table with data.
CREATE TABLE Employee_Database.new_employee AS ( SELECT * FROM Employee_Database.Employee ) WITH DATA;