Re: Migrating an Application from SQL Server 2000 to Oracle 10g

Shakespeare wrote:
"DA Morgan" <damorgan@xxxxxxxxx> schreef in bericht news:1216766476.361735@xxxxxxxxxxxxxxxxxxxxxxxxx
Shakespeare wrote:
"DA Morgan" <damorgan@xxxxxxxxx> schreef in bericht news:1216688701.64031@xxxxxxxxxxxxxxxxxxxxxxxxx
Shakespeare wrote:
"Michael Austin" <maustin@xxxxxxxxxxxxxxxxxx> schreef in bericht news:bIygk.33467$ZE5.10372@xxxxxxxxxxxxxxxxxxxxxxx
One thing I ran into is having to create sequences and triggers for all of those "auto-increment" data types in SQLServer and MySQL that a lot of people like to use for the PK... (which IMO is a complete waste of compute time if the data can be a self-unique PK - but that is just me)

That's a discussion about meaningles and meaningful (primary) keys - imho, data should never be the key itself.

Shakespeare
Are you ready to go toe-to-toe with Joe Celko and many others and
argue that one should never use a natural key?

I certainly hope not.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Never say never. If you're ABSOLUTELY sure the natural key never changes and never duplicates, it's ok with me. Even technical real life keys may cause unexpected problems. I recall a salary payment system that used emplyee numbers as pk's. But then it appeared keys changed when women got married and took their husbands name, just because of some external demand that keys should be in the same order as the names...

Shakespeare
A key never changes. If it can then, by definition, it is not a natural
key.

In the example you used the reason it changes is a business process
decision that is totally irrelevant to the data. They did not need to
change the employee id. They choose to.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

You're right, but in the design process the value was thought never to change, and hence chosen as a primary key. Afterwards, some years later, an external progam needed the numbers to be in order. I just wanted to stress out that it is not always clear on forehand that an attribute will never change. Saying 'if it can change it is not a key' is turning it around. Business processes can change in an unpredictable way.
I worked for a company some years ago that used technical keys only. I have seen both advantages and disadvantages. So to some extent I may go with Joe Celko, but not as a dogma.

Shakespeare

One might well argue that an employee id is, by definition, a surrogate
key. One created by the business for purposes of employee identification
and that the natural key might be tax-id and last_name.

In your case I would argue that once the decision was made to use
employee_id as a natural key then it MUST remain unchanged. Should
someone in upper management decide to alter that decision part of
the cost of that decision necessarily had to include the cost of
rekeying every computer record in the business both currently in
on-line systems and going backward in time for the entire history
of the business. That cost, measured in dollars, would have stopped
the entire nonsense.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: Migrating an Application from SQL Server 2000 to Oracle 10g
    ... Puget Sound Oracle Users Group ... If you're ABSOLUTELY sure the natural key never changes ... Business processes can change in an unpredictable way. ... the cost of that decision necessarily had to include the cost of ...
    (comp.databases.oracle.server)
  • Re: Migrating an Application from SQL Server 2000 to Oracle 10g
    ... Puget Sound Oracle Users Group ... If you're ABSOLUTELY sure the natural key never changes ... Business processes can change in an unpredictable way. ... the cost of that decision necessarily had to include the cost of ...
    (comp.databases.oracle.server)
  • Re: How should I generate a primary key?
    ... And I have agreed that there are cases where a pseudokey is useful. ... the business case that it should be VERY HARD to change ... change the value of the primary key which is more problematic in the field ... The generated key is used as a primary key, but the natural key still sits ...
    (comp.databases)
  • Re: Inserting a new PK into an existing table
    ... David Newman wrote: ... Normally we use a business column or set of column values in the table ... and that an artificial primary key is always preferred. ... If you use a natural key, for example a tax identification number, it ...
    (comp.databases.oracle.misc)
  • Re: Migrating an Application from SQL Server 2000 to Oracle 10g
    ... Puget Sound Oracle Users Group ... If you're ABSOLUTELY sure the natural key never changes ... Even technical real life keys may ... In the example you used the reason it changes is a business process ...
    (comp.databases.oracle.server)