Hello all,

pschmidt@... wrote:

> > The primary key of my customers table is the customer no. Is that
> > wrong ??

> It's not wrong, it's just more work to implement, because you need
> logic to ensure uniqueness, where as a surogate primary key (built
> from a generator), is always going to be unique. What I always do
> is add a field, this field is the tablename_ID (i.e. CUSTOMER_ID)
> this field is created from a generator and is the primary key.
> Other tables use this primary key field for the foreign key. The
> reasoning is that suppose you have a customer number 666, and one
> day the customer company is sold to someone who considers the number
> 666 is extremely bad luck, now you, as a nice caring company, are
> willing to change it to another number 777 (which is considered a
> lucky number), so now you need to find all the occurances of 666 and
> change it to 777,

You don't have to, if you define the foreign keys referring to this PK
with ON UPDATE CASCADE. I always do this, unless there's a good reason
not to.

True, like Helen said: if you use, say, a customer ID as PK in a table
with customer info, and the company later decides to change the
customer ID format, you have to change things. But is it less work if
you have a generated PK and a non-PK column CUSTOMER_ID with a unique
constraint? (Without the unique constraint you risk having customers
with the same ID.)

I also like it when a table (or the entire database, for that matter)
represents as closely as possible the real-life situation. So I only
use generated PKs if the alternative would be a multi-column PK,
which I understand is a Bad Thing.


Greetings,
Paul Vinkenoog