Re: [Info-ingres] Re: is there an equivavlent to auto_increment i n ingres ?

At 12:57 PM +0100 7/20/05, Roy Hann wrote: 
"Ball, David" <David.Ball@xxxxxxxxxx> wrote in message
news:mailman.1121857082.21883.info-ingres@xxxxxxxxxxxxxxxxxx

 2. Next Number ignored
 A heavily concurrent system that used a 'social security' number as the
 prime key in a large number of tables.  This number was not allocated by
us, 
 and had no 'check digit' validation (that we knew of).

Unfortunately, correcting typos in this field meant updating the primary
key 
 in dozens of heavily used tables.

 An artificial 'customer id', allowing the 'social security' number to be
 stored in only one place, would have saved loads of grief.


That made sense in the last century, when I wrote my paper.  But in the 21st
century we have foreign key constraints with an ON UPDATE CASCADE rule.

Zero code, 100% reliable, transparent, self-documenting, and portable.

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"


Roy,

I will start off to admitting my situation is rare, but when
you don't have reliable key data (even though you completely define
the primary key), you HAVE to use a surrogate key.

I actually work on a system where the "client" enters the system
without an established identity.  In fact, the client will often
purposely lie about his identity on purpose.  Clients use identifiers
associated with other clients, sometimes with the intent of creating
confusion.  Worse, clients will "rotate" identities and/or collude
to confuse "the system".  Many clients actually HAVE multiple SSNs,
driver license numbers, FBI file numbers, and state file numbers.

During the course of investigation, officials can SOMETIMES figure
out that two "clients" are actually one and the same, and a "merge"
operation is done.  What complicates this merge is that all the
identifiers involved are STILL not unique after this merge.  Client
A and B might have been discovered to be one and the same, but
Client C may also claim the same SSN, which may actually belong
to neither the AB client or the C client.

So ON UPDATE CASCADE isn't terribly helpful.

Lastly, the client may not be able to give any identifying
information because of unconsciousness, unwillingness, inability,
or ignorance.  The system still needs to track them, by law.

Getting the client to behave better is most business analysts
solution to these kinds of problems.  But when the client is a
person entering the criminal justice system, that is not an
option.

Admittedly, in 20+ years of building systems, this is the first
time I've had a 0-n relationship between a client and the usual
identifiers.  It is ... err ... interesting.

Cheers,

--
Michael Leo               Java, J2EE, BEA WebLogic,
Caribou Lake LLC          Oracle, Open Source, Ingres,
mleo@xxxxxxxxxxxxxxx      Real Enterprise Applications
.