Re: Call for testing experimental patch

[David Tangye <..hidden..>]

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?)
 - 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. 
 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.

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.