On 9/12/06, David Tangye <..hidden..> wrote:
On Tue, 2006-09-12 at 23:01 -0700, Chris Travers wrote:
> If the id is duplicate, it will violate the unique constraint on the
> shadow table (id_tracker) when it is inserted and the transaction will
> fail.
>
> Or am I missing something?
Well I have not been a DBA much for about 10 years now, but
- I can't see that existing rows are covered in your patch? (add a
migration script to add these in?)
That is what the insert's are for :-) If they fail you have problems....
- Its a step forward, but surely its better to fix the original tables,
by placing unique constraints there. Oh, are we saying that the id needs
to be unique across all tables? Surely not.
All in good time. That requires a lot of code reworking and a
fundamnetally different db architecture. I am sorry but I dont
think that is practical in a short time and people are better to have a
fix now.
Think of this as "scaffolding."
1. If we need a unique id across groups of tables, then each group
should have its own 'id', and
2. When doing data analysis this usually points to an implied/phantom
entity, which leads to the possibility of entire table(s) being missing.
3. Cant some permanent fixup code like 'update (sequence) set id =
(select max id + 1 from (id from unioned view of tables)' be placed into
somewhere like a database integrity module/menu-item/upgrade scripts?
Having said that I would be concerned that the database integrity gets
lost in the first place. Its schema must be redefined so this cannot
ever happen, irrespective of what DML code is run against it.
Agreed/
The meaning/usage of 'id' (and 'num' and 'code'?) needs to be defined
and consistently used, otherwise you get confusion and bugs written into
the system down the track.
Agreed. But again, do we rewrite the app from scratch which
is what would be required, or do we stabilize and fix in place?
Best Wishes,
Chris Travers