TERADATA CONSTRAINTS AND PRIMARY INDEX PRACTICALS(IN-DETAIL)

CONSTRAINTS PRACTICALS:
=========================
SYN:
COLNAME DATATYPE CONSTRAINT <CONSTRAINTNAME>
PRIMARY KEY/UNIQUE/CHECK/REFERENCES (COLS)
 NOTE:
 CONSTRAINT NAME IS HELPFUL TO MODIFY /REMOVE EASILY
CREATE MASTER TABLE(dept)
======================
CT DEPT(DID INTEGER CONSTRAINT PK PRIMARY KEY NOT NULL,
      DNM VARCHAR(30),DINC INTEGER CHECK (DINC<40000))
INS DEPT(10,'XXX',20000)--SUCCESS
INS DEPT(10,'XXX',30000)--FAILS DUE TO UNIQUE PRIMARY KEY ERROR
INS DEPT(20,'YYYY',50000)--FAILS DUE TO CHECK
SEL * FROM DEPT
CREATE CHILD/TRANSACTION TABLE WITH HARD RI(EMP)
=================================================
CT EMP(EID INTEGER UNIQUE NOT NULL,ENM VARCHAR(30),DID INTEGER
          REFERENCES DEPT(DID)
          )
 SHOW TABLE EMP
 INS EMP(1,'X',10)--SUCCESS
 INS EMP(1,'Y',10)--FAILS DUE TO UNIQUE PRIMARY KEY ERROR
 INS EMP(2,'Y',20)--FAILS DUE TO HARD RI
 SEL * FROM EMP

CREATE CHILD/TRANSACTION TABLE WITH SOFT RI(EMP1) AND COMPOSITE PK
=====================================================================
CT EMP1(EID INTEGER  NOT NULL,
 ENM VARCHAR(20) NOT NULL,
 DID INTEGER  REFERENCES WITH NO CHECK OPTION DEPT(DID),
 PRIMARY KEY(EID,ENM)--COMOSITE KEY/TABLE LEVEL CONST
          )
 SHOW TABLE EMP1
 INS EMP1(1,'X',10)--SUCCESS
 INS EMP1(1,'Y',10)--SUCCESS
 INS EMP1(1,'Y',10)--FAILS DUE TO UNIQUE PRIMARY KEY ERROR
 INS EMP1(2,'Y',20)--SUCCESS DUE TO SOFT RI
 SEL * FROM EMP1

 DROPPING TABLES
 ==================
 A) CHILD TABLES FIRST
 B) MASTER TABLES (REFERENCE TABLES)
 DROP TABLE EMP
 DROP TABLE EMP1
 DROP TABLE DEPT





















PRIMARY INDEX PRACTICALS

=========================================================================
SYN:
CREATE TABLE (COLS)[UNIQUE/NO] PRIMARY INDEX(COLS) [PARTITION BY [CASE_N/
RANGE_N...] (COLS))

EX:
UPI->CT R1(TID INTEGER,TNM VARCHAR(30)) UNIQUE PRIMARY INDEX(TID)
SHOW TABLE R1
NUPI->CT R2(TID INTEGER,TNM VARCHAR(30)) PRIMARY INDEX(TID)
SHOW TABLE R2
PPI(NORMAL)->CT R3(TID INTEGER,TNM VARCHAR(30),TCD INTEGER)
    PRIMARY INDEX(TID) PARTITION BY TCD
SHOW TABLE R3
PPI(CASE_N)->CT R4(TID INTEGER,TNM VARCHAR(30),TCD INTEGER)
PRIMARY INDEX(TID)
PARTITION BY CASE_N(TCD=10,TCD=20,NO CASE,UNKNOWN)
==================================
PPI(RANGE_N)->CT R5(TID INTEGER,TNM VARCHAR(30),TCD INTEGER)
PRIMARY INDEX(TID)
PARTITION BY RANGE_N(TCD BETWEEN 0 AND 60 EACH 20)
======================================
MLPI->JDATE FIRST PARTITION,WITH IN THE JDATE TCD NEXT PARTITION
CT R6(TID INTEGER,TNM VARCHAR(30),TCD INTEGER,JDATE DATE)
PRIMARY INDEX(TID)
PARTITION BY
(
RANGE_N(JDATE BETWEEN '2015-01-01' AND '2015-12-01'
EACH INTERVAL '1' MONTH),
CASE_N(TCD =10,TCD=20,NO CASE,UNKNOWN)
)
==================================================
SYN:
PARTITION BY
(
LEVEL1 ,
LEVEL2,
LEVEL3
...
LEVEL 62
)
================================
NOPI-> TWO WAYS TO CREATE
1ST WAY->CT R8(TID INTEGER,TNM VARCHAR(30)) NO PRIMARY INDEX
2ND WAY-> CT R9 AS (SEL * FROM R6) WITH NO DATA NO PRIMARY INDEX

CONVERTION FROM NOPI ->PI
============================
X(NOPI)-->WANT TO CONVERT X(PI)

1. TAKE BACKUP OF X
2. CREATE SIMILAR TABLE OF X AS Y WITH A PROPER PI
3. LOAD X->Y (DATA DISTRIBUTED)
4. DROP X
5.  RENAME Y->X

MODIFYING PRIMARY INDEX(IF TABLE IS EMPTY AND NO NOPI ASSOCIATED)
========================
ALTER TABLE <TNM> MODIFY PRIMARY INDEX(COLS)
EX:
SHOW TABLE R1
ALTER TABLE R1 MODIFY PRIMARY INDEX (TNM)

DEFAULT PRIMARY INDEX
===========================
IF WE DO NOT DEFINE ANY INDEX SYSTEM FOLLOWS THE BELOW PROTOCOL
A) PRIMARY AND UNIQUE COLUMNS CONSIDER AS UPI COL
B) BOTH P[RIMARY AND UNIQUE AVAILABLE GIVES HIGH PREFERENCE TO PI
C) NO PRIMARY /UNIQUE THEN IT WOULD TAKE NUPI ON FIRST COLUMN
D) IF PI ASSIGNED EXPLICITLY/INTERNALLY REMAINING PK/UK COLS
    CONSIDERED AS USI COLUNS (UNIQUE SECONDARY INDEX)
 
SCENARIOS:
1.   CREATE TABLE T1(TID INTEGER,TNM VARCHAR(30))
 NUPI (TID)
2.   CREATE TABLE T1(TID INTEGER,TNM VARCHAR(30) PRIMARY KEY NOT NULL)
UPI(TNM)

3.   CREATE TABLE T1(TID INTEGER UNIQUE NOT NULL,TNM VARCHAR(30) PRIMARY KEY NOT NULL)


UNPI(TNM) USI(TID)  
4. CREATE TABLE T1(TID INTEGER UNIQUE NOT NULL,
    TNM VARCHAR(30) PRIMARY KEY NOT NULL,TCD INTEGER)
    UNIQUE PRIMARY INDEX (TCD)
    USI(TID),USI(TNM)