I am stuck with a strange situation ....I am having about 100 tables in my system which are
SET+UPI defined upon that SET----system level check to ensure no duplicate rows are there UPI----table level check to ensure a particular column has no repeated values thus identifying a row uniquely
My doubt is that will SET+UPI combination will do duplicate row checking or only single time checking will be done?
Also what is the process that really goes i mean to say 1st SET is checked then UPI check is done or it's vice versa... Please clarify as i need to change all 100 tables if SET+UPi is a problem...Also please clarify how really SET checking and UPI checking is done by Teradata while inserting data
If a table has any unique index (primary or secondary) there is never any duplicate row checking. Checking for duplicate rows would be redundant in that case, so the Teradata software recognizes that it is not necessary.
This may be not true for INSERT...SELECT's. If there is an UPI on a SET TABLE, Teradata silently removes duplicates BEFORE the INSERT and there will be no 'Duplicate unique prime key error' (thus the 'duplicate checking' occurs no matter if there is an UPI or not).
Hi Carlos, I am bit confused as which one of you is saying correct ,also i was unable to understand anything as all in spanish Could you please write something from that extract in english explaining how really teradata checks SET command i.e mechanism involved in it as it is important for me to present all aspects in front of team to remove those 100 tables from SET to MULTISET