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

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 23 Jul 2008 09:39:17 +0200
Message-ID: <4886e035$0$49763$e4fe514c@news.xs4all.nl>

"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1216766476.361735_at_bubbleator.drizzle.com...
> Shakespeare wrote:

>> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht 
>> news:1216688701.64031_at_bubbleator.drizzle.com...
>>> Shakespeare wrote:
>>>> "Michael Austin" <maustin_at_firstdbasource.com> schreef in bericht 
>>>> news:bIygk.33467$ZE5.10372_at_nlpi061.nbdc.sbc.com...
>>>>> 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_at_x.washington.edu (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_at_x.washington.edu (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 Received on Wed Jul 23 2008 - 02:39:17 CDT

Original text of this message