|
|
Archives of the TeradataForum
Message Posted: Thu, 01 Dec 2005 @ 15:42:04 GMT
Subj: | | Re: How to leave an intentional lock for testing |
|
From: | | Al MacGowan |
From: Roth, David
| What is your fight with identity columns? | |
The shortest answer I can give is this... We experimented with 4 tables, using identity columns. It's my fault that I allowed it to happen,
and now it's my problem. We implemented a table/data-based scripted utility that was developed by someone else. They had it implemented on
Oracle previously, and had used a DECIMAL/"ID" column to uniquely identify rows in these 4 tables. But they had to insert a "0" into this column,
and then a trigger did a SEL MAX()+1, updating the newly added row.
So, having a previous background in MSSQL, I thought - well, I'll just go IDENTITY on those tables and not worry about it, IDENTITY columns
work like a charm! WOW was I wrong - I've been paying for it ever since, with DUPLICATE PRIME KEY errors and what I would consider to be
unexpected results. Now, I've taken a step back and said "I'm going to actually think about these tables and not just implement them as was asked
of me". It appears that the utility/script doesn't ever worry about or need the "ID" column for anything. I'm about to start experimenting with
the script running with some alternate uniqueness, with the IDENTITY col dropped.
Another torturous thing about IDENTITY columns are that you cannot use the CREATE TABLE AS command with them - and I have grown to really love
that command, and wish to keep using it :)
Alistair MacGowan
Senior Database Administrator
| |