PPI error

Database
Enthusiast

PPI error

I am trying PPI
CREATE SET TABLE Employee
(Employee_Number INTEGER NOT NULL
,Location_Number INTEGER
,Dept_Number INTEGER
,Emp_Mgr_Number INTEGER
,Job_Code INTEGER
,Last_Name CHAR(20)
,First_Name VARCHAR(20)
,Salary_Amount DECIMAL(10,2))
UNIQUE PRIMARY INDEX (Employee_Number)
PARTITION BY Location_Number;

It is throwing an error ----Location_Number shud be a part of PI for its uniqueness....

I have read that Location_Number should be a part of PI to make it UNIQUE....
But not able to figure out why....
Can anyone please explain why Location_Number needs to be included although Employee_Number is unique....
Please provide some example....
4 REPLIES
Enthusiast

Re: PPI error

There was a long discussion on this already.

http://www.teradata.com/teradataforum/Topic6553-9-1.aspx
Enthusiast

Re: PPI error

Hi Joedsilva,
Thankx for that link....it was quite useful...but my doubt is somewhat different

UPI(COL1,COL2,COL3)
PARTITION BY RANGE_N (col3 BETWEEN DATE '2008-01-01' AND DATE '2008-12-31' EACH INTERVAL '1' MONTH );

QUERY----
Given that I was inserting record (409, 33,'2008-12-11') EVEN THEN IT HAS TO MAKE SURE THAT 409,33,'2008-12-11' IS NOT PRESENT.......THAT IS IT HAS TO CHECK ALL PARTITIONS......IF I AM CORRECT...

DOES THAT MEANS ALL COL1,COL2,COL3 SHOULD BE THERE IN PARTITION BY CLAUSE TO MAKE IT REALLY AN UPI...???
PLEASE CLARIFY.......
Enthusiast

Re: PPI error

It needs to check only in one partition, the partition that corresponds to col3='2008-12-11'

That's because it's perfectly valid to have another (409, 33,col3) as long as that col3 is not '2008-12-11' .
Enthusiast

Re: PPI error

Hi Joel
I agree with whatever you are saying Joel
But I am talking about the scenario...

Rows to be inserted are
1--(409, 33,'2008-12-11')
2)(409, 33,'2008-12-12')

or
1)----((409, 33,'2008-12-11')
2)---(409, 34,'2008-12-11')

Can you tell me how each partition check wont be necessary in above two cases...
To make sure this it has to again search for all partitions.....as per your conclusion in that discussion