Re: Migrating an Application from SQL Server 2000 to Oracle 10g
From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 22 Jul 2008 15:41:21 -0700
Message-ID: <1216766476.361735@bubbleator.drizzle.com>
>
> 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
Date: Tue, 22 Jul 2008 15:41:21 -0700
Message-ID: <1216766476.361735@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.orgReceived on Tue Jul 22 2008 - 17:41:21 CDT