Re: Surrogate Key Semantics

From: Eric Junkermann <eric_at_deptj.demon.co.uk>
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.
>

No, they are not. Fields/records/files are physical things, which may or may not be used in an implementation supporting the logical structure of tables with rows and columns. If you are not aware of the distinction, you will not be aware of possible and/or desirable methods of processing the data.

>
>>>>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.

To put it another way, you have a surrogate key if you have to make it up when you insert a row. In a referencing table, you don't get to make anything up, you have to use the key of the row in the referenced table.

-- 
Eric Junkermann
Received on Sun Nov 13 2005 - 19:43:06 CET

Original text of this message