Archives of the TeradataForum

Message Posted: Fri, 14 Oct 2005 @ 19:04:27 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

What bugs ?

We are using the identity column quite heavily under V2R5.0 and have not seen any problems.

The only thing, which is a bit tricky is, that, once you have to make a copy of a table with an identity column, you may not be able to supply uniqueness anymore. The new ( copied ) table will get a new pool of IDs for the identity column. The new pool is then not aware of the already used IDs in the copied table.

If you want to use the identity column as a surrogate key, this is crucial.

We have implemented the following logics:

While loading process we include 2 columns to a intermediate table in a staging area. This table has 2 additional columns:

1 column ( id_load ) holds an ID_LOAD, which is incremented for each load process.

1 column ( autonum ) is an identity column created by the system.

In the "next step" ( final insert into <DESTINATION TABLE> we concatenate these two columns into one and create a unique "surrogate key" with a statement like

     Cast ( id_load || cast( autonum as integer format '99999999') as decimal(18,0) )

As we found, this is a very convienient solution to create a unique artificial key without the danger of screwing up the whole thing in case of copying or restoring some tables.

So use the identity column only in temporary tables to guarantee uniqueness.

Christian

------------------------------
makeITdone IT Services
D.I. Christian Schiefer

	<Prev	Next>		<<First	<Prev	Next>	Last>>