Duplicate Row Warning: Teradata

[horserider]

I have a flat file that goes into a TERADATA Table in a parallel job. The TERADATA Table has NON UNIQUE KEY. But when I load the source data into the target table the Director displays warnings about the duplicate rows and rejects them.

When I am creating teradata table and the index in NON-UNIQUE, why does DataStage rejects duplicate rows? How to address this issue?

I am using Teradata PX Stage to load the data.

[throbinson]

Please post the exact message. Why do you think it is being generated by DataStage and not Teradata?

[horserider]

I guess I should have been specific.

I don't know if it's teradata or datastage that is rejecting the row. This is the DDL to create Table.

CREATE SET TABLE EMPLOYEE_STG ,NO FALLBACK
(
YR_MTH CHAR(6),
AD_CO_NBR CHAR(5),
DLR_PA_C CHAR(5),
DLR_NBR CHAR(5),
VIN CHAR(17),
AMOUNT DECIMAL(9,2),
VO_CD CHAR(1),
R_DT CHAR(6),
ADV_REC CHAR(2),
DATE_UPDATE_Y DATE NOT NULL DEFAULT DATE,
TIME_UPDATE_M TIME(0) NOT NULL DEFAULT CURRENT_TIME(0)
)
PRIMARY INDEX PRIMARY_KEY (YR_MTH, AD_CO_NBR,DLR_PA_C,VIN);
;

Since I am not defining as UNIQUE PRIMARY KEY, my guess was I should have been able to insert duplicate data. I am really surprised to see that Teradata doesn't allow to insert duplicate rows as we see in DB2, Oracle, SQL Server and other databases

[ray.wurlod]

Is there not an automatic "jointly unique" constraint added when you include PRIMARY KEY in a CREATE TABLE statement?

[throbinson]

It is the difference between a set table and a multi-set table. A set table refers to set theory which mandates that each tuple be unique. If you don't want that then define the table as a multi-set table which will allow multiple "sets" of data.

[ray.wurlod]

And a set table is the default, is it not? Therefore horserider's DDL will create a set table which will enforce uniqueness of keys.

[throbinson]

It will enforce uniqueness of keys indirectly since it is enforcing uniqueness of the entire row (set) when defining the default as a set table. I took his question to be why doesn't Teradata behave like other DBMSs where rows can be duplicated until the cows come home as long as the Primary key is non-unique or even missing.
This is the difference between set and multi-set tables in Teradata.
Uniqueness is not strictly a "key" concept in Teradata. It is also a row concept when defining a set table.