We can create SET/MULTISET tables in Teradata. There are few differences between SET and MULTISET tables.Let’s discuss about syntax to create these tables.
Create Table Syntax:
We have to specify the table type,table options,column definitions and Index definitions in the create table statement of Teradata.
CREATE SET TABLE BANKING_DB.CUSTOMER ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
Cust_Id INTEGER,
Cust_Name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
Join_Date DATE FORMAT 'YYYY/MM/DD',
Mobile_Number INTEGER,
Login_Count DECIMAL(18,0)
)
UNIQUE PRIMARY INDEX ( Cust_Id );
The Teradata SET table did not allow duplicate values in table.In SET table,we need to specify the Unique primary index in the index definition area which helps teradata to avoid the duplicate values.
Create MULTISET table example
CREATE MULTISET TABLE Banking_DB.Cust_Transaction ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
Cust_Id INTEGER,
Transaction_no VARCHAR(20),
Transaction_Date DATE FORMAT 'YYYY/MM/DD',
Amount INTEGER,
Balance DECIMAL(18,0)
)
PRIMARY INDEX ( Cust_Id );
MULTISET tables allow duplicate values in the table.We can create the MULTISET table without specifying the Index definitions such Primary Index or Unique Primary Index. By default,it allow the duplicate values if we not specifying index definitions.
