During the data modelling process, the RI i.e the primary and foriegn key relations are defined based on the business requirement and this process is called the LDM(Logical data model).This will be an input to PDM(Physical data model) wherein the Primary Index is defined.ERWIN is one of the data modelling tool.
In teradata the data distribution,data retrieval and joins are all based on the primary index.The primary key can be a PI but not necessarily.In the create DDL we do not normally specify the primary key but it can be defined using CONSTRAINT as below
Primary key constraint can be established at column level & table level
CREATE TABLE DB.EMPLOYEE
(
EMP INTEGER NOT NULL
CONSTRAINT EMPPK PRIMARY KEY
,DEPT INTEGER CONSTRAINT REF_1 REFERENCE DEPARTMENT(DEPT)
);
Foriegn key constraint can be established at table level only
CREATE TABLE DB.EMPLOYEE
(
EMP INTEGER
,DEPT INTEGER
,CONSTRAINT EMPPK PRIMARY KEY(EMP)
,CONSTRAINT REF_1 FORIEGN KEY(DEPT) REFERENCES DEPARTMENT(DEPT)
);
