Re: Oracle NULL vs '' revisited

> That statement just earned you a place in my class at the university.
> Would you like to reconsider it before you earn a place of infamy?
> Natural keys can not change. If it can change IT IS NOT A KEY it is
> just a value.
>

These are some examples of a natural key that have changed in the past 10 years ...

ISBN number

ISO country code

Telephone number here in London if you where stupid enough to have used it as a natural key (it is afterall unique).

Country name if you where stupid enough not to use the ISO code, how many country name changes have there been in the last 10 years?

> Good grief!

Yes, I'm shocked you didn't know these industry keys you should be using as your natural keys in database design have changed.

> ID FNAME LNAME
> 1 Daniel Morgan
> 2 Jack Cline
> 3 Daniel Morgan
>
> That surrogate key accomplished precisely what?

And where is your natural key?

Also, you obviously haven't worked or designed a CRM system where the above happens except you have other attributes to better identify the entity you've modelled, but again - you'd have a form of natural key or if not you'd have to make one using an artifical key in which case you'd probably not need a surrogate because the artificial would not change.

Let me pose a question [QUESTION TO ANSWER SO NO DISTRACTION AND IGNORING IT].

Given natural keys change, eg. ISBN and ISO country code.

If the user browses to your site and gets the data then he currently holds the natural key 12345 which is the link back into the database and data; the user is disconnected (it's a browser - IE for instance), you have not used a surrogate key - just a natural key.

How on earth do you reconnect the user with the data, which in the database the natural key changed to 12345-02?

The answer is you can't without using a surrogate key, or is there some magic I don't know about - remember, without using a surrogate key?

All you have shown here is yet another example that you are an objectional idiot who is more bent on being 'right' in his own mind then being 'right' along with his peers; the page where you inaccurately rubbish SQL Server v Oracle is also an example.

If you didn't know natural keys changed then what are you doing teaching? Obviously you have just learn't syntax and have no real industrial experience like somebody else I know.

PS. I'm awaiting your email reply to why this isn't an equiv "system trigger" in SQL Server compared to your Oracle LOGON trigger....

Link: http://technet.microsoft.com/en-us/library/ms189799.aspx

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND

    (SELECT COUNT(*) FROM sys.dm_exec_sessions

            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3

-- 
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"DA Morgan" <damorgan_at_psoug.org> wrote in message 
news:1187911246.647202_at_bubbleator.drizzle.com...


> Tony Rogerson wrote:

>>> Ah, that is why you like those so much Tony. You are forced to use a

>>> crutch and have grown to like it. You have my deepest sympathies.

>>

>> Unlike yourself Ed I realise the natural key may change;

>

> That statement just earned you a place in my class at the university.

> Would you like to reconsider it before you earn a place of infamy?

> Natural keys can not change. If it can change IT IS NOT A KEY it is

> just a value.

>

> Good grief!

>

>> and unlike yourself Ed I prefer to protect myself from the concurrency 

>> problem caused by a disconnected application archiecture - basically, the 

>> web broweser as an example; surrogate keys are a known, tried and tested 

>> solution for that.

>>

>> That class room door you hide behind needs oiling.

>

> Look who's talking. And while you are reconsidering, and you should,

> your response to Ed consider this:

>

> ID   FNAME     LNAME

> 1    Daniel    Morgan

> 2    Jack      Cline

> 3    Daniel    Morgan

>

> That surrogate key accomplished precisely what?

>

> Amazing!

> -- 

> Daniel A. Morgan

> University of Washington

> damorgan_at_x.washington.edu (replace x with u to respond)

> Puget Sound Oracle Users Group

> www.psoug.org