Re: Surrogate Key Semantics
Date: Sun, 13 Nov 2005 18:43:06 +0000
Message-ID: <vRX+t9F6k4dDFwaz_at_deptj.demon.co.uk>
In message <1131865855.158377.237530_at_g44g2000cwa.googlegroups.com>,
jonshin2003_at_yahoo.com writes
>Thanks for the response...I guess.
>
>>>>Let's get back to the basics of an RDBMS. Your questions are all based
>>>>on the wrong mental model.
>
>What should the "mental model" be?
>
Yes, indeed, Mr Celko, please explain. And while you're at it, explain what you think the OP's mental model is and why it is wrong.
>
>>>Read some of Dr. Codd's stuff on this.
>
>All the data, nothing but the data...blah, blah. I want practical
>answers.
>
Practical answers based on practical experience are a good idea, except that your current problem, whatever it is, is almost certainly subtly different. If you know the theory, you may spot this and avoid problems or even disaster.
>>>BY DEFINITION, you cannot name a surrogate key because it is hidden from you.
>
>Yeah, you can. A.ID is a made-up key for table A. I call it a
>surrogate key.
>
And so do a lot of other people. Just where, Mr Celko, is there a definition equating the term "surrogate key" with "implementation-provided key hidden from designers/programmers as well as from end-users"?
>>>>Rows are not records; fields are not columns; tables are not files.
>
>Yeah, they are.
>
>
>>>>RDBMS uses the terms "referenced" and "referencing" tables. And it
>>>>does not say HOW that referencing is done. So anything like what you
>>>>call a surrogate will depend on the implementation, not on RDBMS.
Where do you get that from? Ah, yes, your definition of "surrogate". And are you sure you know the difference between an RDBMS and the Relational Model?
>
>What? Databases define relationships with primary and foreign keys?
>
Indeed they do.
>What are you talking about? Can you just answer my question?
>
He doesn't know as much about what he is talking about as he thinks he does.
As for the question...
Table A has a surrogate key, because you chose to use a key which is not part of the real world data. (There is a potentially very long discussion on why you should want to, and whether you should, but I don't think we want to discuss that now - if anyone does, start another thread!)
Table B has a foreign key referring to table A. That's all you need to say about it - it is not a surrogate key, it just refers to one - it has to refer to whatever you choose to be the key of table A.
Same thing for the second question, and for any depth or breadth of reference.
-- Eric JunkermannReceived on Sun Nov 13 2005 - 19:43:06 CET