jwcolby
jwcolby at colbyconsulting.com
Thu Mar 3 13:46:39 CST 2011
LOL, Pretty much nonsense in my book. > Data uniqueness though is not a separate issue. (just one example) http://www.1keydata.com/sql/sql-primary-key.html >>>*A primary key is used to uniquely identify each row in a table.* My PK does that! >>>It can either be part of the actual record itself , *or it can be an artificial field (one that has nothing to do with the actual record).* That would be my PK. In MY world, a PK is a FK in another table. It is a pointer back to the parent. That is it's only purpose! PERIOD. (full stop) >In fact it goes to the very heart of a relational design. And has absolutely NOTHING whatsoever to do with the pointers between records. PERIOD. (full stop) YOU (personally and individually) MAKE it have something to do with the pointer, but I do not. And I do not have to, and neither do you. You CHOOSE to make them "related" but they are not. > With a relational design, you start with a relation (a table). Yad yada yada *ad nasium* (meaning this crap makes me physically ill). And having absolutely NOTHING to do with the pointer between records. I have been doing this since the early 90s. Please save this for your intro to databases class. > When you simply add an auto number to a table, even though it is labeled as a "PK" it does not perform the job of one, because it only identifies a row uniquely in a physical aspect, not a logical one. Hmmm, I guess you need to take up your argument with Microsoft, not me. I open Access and create an autonumber and click a button and MICROSOFT calls it a PK. Therefore I call it a PK. I open up SQL Server, and I create a autoincrement and click a button and Microsoft calls it a PK. Therefore I call it a PK. I drag and drop this PK between the parent and child and the database engine sets up rules designed to enforce referential integrity. I guess what I am saying is I simply don't give a rat's patuty about academic horsepucky. What I care about is that I have a pointer and I have a unique index on a set of fields which guarantees *data* uniqueness and guess what... The two are not related. <gasp> and... It works! <gasp> So I have proven that *my* pointer and *my* data uniqueness are completely and totally unrelated, and *you* are simply arguing that I cannot call it a PK. *Don't care*. *Take it up with Microsoft*. When they stop calling it a PK so will I. I will then call it whatever they call it. In the meantime, we (as a functioning society of database designers) need a common vocabulary. Microsoft is calling my pointer a PK so I pretty much have to or I have to copy your page of horse pucky into every email to explain myself. *not happening* Jim, this happens whenever this subject comes up. Some (typically) oldtimer who went through college back when Codd was a young man and the god of database starts talking in academic terms, tuples and relations and all of that stuff. I am happy you like it but I have never actually uttered the word tuple in my life, it makes no difference to me. I get along just fine with tables, rows and fields. I understand what goes in each of those things. It is second nature (and trivial) to normalize to 3rd normal. I have read many though not all of the rest of the 16 normal forms and understood (at the time I read it) *some* of them. Most seemed oh so esoteric. If I sound uninterested, or even argumentative, it is simply because I find this whole thing quite easy at the real life level, and I have used (what I was told to call) surrogate PKs since oh... about 1994 and it just works. So to get into a peeing match about my calling this thing a PK is just silly. John W. Colby www.ColbyConsulting.com On 3/3/2011 10:02 AM, Jim Dettman wrote: > John, > > <<My definition of a PK is: > > 1) A field or set of fields which uniquely identifies any given record > 2) *AND IS USED TO DO THAT*. > > NOTICE that I am not discussing *data* uniqueness here. *THEY ARE > COMPLETELY SEPARATE ISSUES*. >>> > > Data uniqueness though is not a separate issue. In fact it goes to the > very heart of a relational design. When you model data relationally, it is > the logical organization of data and its actual meaning that is being worked > with. The aspect of how that model is physically implemented is not a > consideration at all. > > With a relational design, you start with a relation (a table). Rows are > instances of whatever your modeling and columns are the attributes. The > combination of one or more attributes *must* yield a unique key. If not, > then you don't have a proper relation and must add more attributes. > > When you simply add an auto number to a table, even though it is labeled as > a "PK" it does not perform the job of one, because it only identifies a row > uniquely in a physical aspect, not a logical one. To do the latter, you > need to tack on another index, which represents either the true primary key > for the data, one of the candidates, or a super key. > > However it can be made into a surrogate PK by assigning it to the object > it's associated with, or in other words, making it an attribute. > "Surrogate" means "to take the place of" and an auto number when it's just > applied to a table cannot do that because it has no meaning. It's a pointer > or tag in a physical context and that's it. Yes it is unique, but I can go > in and change it at will. > > However if it is assigned to the object it's associated with and turned > into an attribute, then it becomes a surrogate PK. An example of that > would be handing it to a customer and using it as a customer code. Once I > do that, I now cannot go in at will and change it now without informing the > customer. Its been given meaning in a logical context. > > Jim.