Need update on Identity columns

Database

Need update on Identity columns

Hi,

We have a requirement to have a automatic sequence generator column in our table, some thing like the below:

      SEQ_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY

           (START WITH 1 

            INCREMENT BY 1 

            MINVALUE -2147483647 

            MAXVALUE 2147483647 

            NO CYCLE)

We created it, but after that our procedure is failing with below error:

Invalid MERGE-INTO statement: Target table with Identity column as primary index is not allowed with WHEN NOT MATCHED specifiction

 

We cannot do any changes to the Merge statement. My Question is,  is there a way to create Identity column or  automatic squence generator (which we can consider as Unique Index) in Teradata. Because we need a unique index for our framework to run sucessfully but in our business logic we dont have any unique index column requirement, that is why we tried to create an Identity column, which would also work as a UI.

 

Regards,

-AP

2 REPLIES
Enthusiast

Re: Need update on Identity columns

Hi,

According to the Merge Into documentation, PI can be identity column, it was restricted with Upsert form of update. 

What if you generate the ID in some tamp table and then insert into this UPI column along with the other data during loading process?

Khurram

Re: Need update on Identity columns

Thanks for your inputs. No we cant create a Temp table, its a restriction. Is there any other way to Handle this ?