Identity Column as Composite UPI

Database
N/A

Identity Column as Composite UPI

Hi All,
I have a identity column in my table and i ve to use it in a composite UPI...I tried but I cant use that , i got err with "Illegal use of identity column" ..so i used it as USI and the Other coloumns as a UPI ..i think the reason for that err is the Identity col is a System generated one..so we cant use it as composit UPI......can anybody tell me some other reason why it cant be used....?
7 REPLIES
N/A

Re: Identity Column as Composite UPI

Hi

Is it the first column of UPI?

Re: Identity Column as Composite UPI

No it is not the first column
N/A

Re: Identity Column as Composite UPI

it doesn't matter if it is the first column or not. You get the error either way. I don't know why you can't do this. I also can't figure out why you'd want to.

Re: Identity Column as Composite UPI

hi,
as per the Teradata Documentations, an identity column can not be a part of a composite primary index.
pls refer the TD docs for further reference.
N/A

Re: Identity Column as Composite UPI

I have been trying to create a composite index using identity column but teradata does not allow that too. I think one just can't create a composite index using identity column either it is primary index or secondary index.

this is bad. :(

Re: Identity Column as Composite UPI

Hi,

Yes Teradata will not allow to create any composite index on Identity column.

To have composite index on the sequence generated column you can have the Identity column in a new table along with the columns used to generate unique number(Will be like Cross Reference table), and use the records from new table and source records to load the target table.

In this approach you will not have any Identity column in the Target table, so we can create any kind on index on this table.

Regards,

Balamurugan

Re: Identity Column as Composite UPI

CREATE MULTISET TABLE dbname.tablename ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( SYSTEM_CODE VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC, SEQUENCE_NUM INTEGER TITLE 'Sequence Number' NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 2147483647 NO CYCLE), SOURCE_ID VARCHAR(3) CHARACTER SET LATIN CASESPECIFIC) PRIMARY INDEX XNUPI_ORDER_DOC_ADDRESS_NOTES ( SYSTEM_CODE, SEQUENCE_NUM );

[NCR] [Teradata DBMS] : Illegal usage of Identity Column SEQUENCE_NUM. [SQL State=HY000, DB Errorcode=5784]

How to avoid this error? Ans: Try to use only the Identity column as the PI. It is allowed.

Note: It will help if you are okay with this single column PI.