How do we generate surrogate keys in teradata ?

Database
Enthusiast

How do we generate surrogate keys in teradata ?

Hi,

How do we generate surrogate keys in teradata, kindly explain with an example?

I know the basic concepts of surrogate keys, what I need is how do we implement it in teradata?

From one of the teradata PDFs, I read that it can be generated with help of Identity columns, can someone provide me the syntax for the same.

Also I would like to know, is there any other method available to generate surrogate keys in teradata?

Regards,
Balamurugan
4 REPLIES
Enthusiast

Re: How do we generate surrogate keys in teradata ?

Why dont you try CSUM function? That might be simpler.
Enthusiast

Re: How do we generate surrogate keys in teradata ?

CREATE TABLE TESTDB.TEST1 (
EMPNO NUMERIC(10) GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1),
ENAME VARCHAR(50)
) ;

OR

Use CSUM function
cumulative (or running) sum of a column expression
Enthusiast

Re: How do we generate surrogate keys in teradata ?

Hi Rama,Shakir thanks for your inputs!!

Regards,
Balamurugan
Enthusiast

Re: How do we generate surrogate keys in teradata ?

beware with identity columns. they will always generate unique values but not sequential. so there would be gaps in numbers.
best approach would be to increment the surrogate key in your transform script which is a standard data warehousing practice and not limited to teradata. there is no teradata equivalent of oracle sequences which is used by OLTP systems to generate their keys mostly.