tag:blogger.com,1999:blog-6411920579549337139.post2433568675698728023..comments2023-12-31T05:26:17.608-08:00Comments on DATABASE DEBUNKINGS: A New Understanding of Keys Part 1: Primary Key Formal Mandate and Pragmatic SelectionFabian Pascalhttp://www.blogger.com/profile/01346669716885494092noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-6411920579549337139.post-79403696368701158132018-04-17T23:54:50.423-07:002018-04-17T23:54:50.423-07:00You need to read the full paper. Natural key is an...You need to read the full paper. Natural key is an industry, not relational term.<br /><br />FOPL requires a PK to be a name, not a property. If there is a simple CK, it is designated PK. If not, a SK is generated, BUT (1) it must be managed exclusively and transparently by the DBMS and (2) it must have a 1:1 with some non-simple CK.Fabian Pascalhttps://www.blogger.com/profile/01346669716885494092noreply@blogger.comtag:blogger.com,1999:blog-6411920579549337139.post-2652715955899283862018-04-17T23:40:48.458-07:002018-04-17T23:40:48.458-07:00I would phrase it otherwise. The 1:1 cardinality ...I would phrase it otherwise. The 1:1 cardinality between ck and *some* unique attribute is what makes the primary key either a surrogate (computer generated) or a natural key. There is no such thing as a natural key. Any natural key is priorly a surrogate key.Racim Boudjakdjkhttps://www.blogger.com/profile/12722817089228369700noreply@blogger.comtag:blogger.com,1999:blog-6411920579549337139.post-38019207791805995112018-04-16T20:20:01.551-07:002018-04-16T20:20:01.551-07:00And by buying the paper you will also support the ...And by buying the paper you will also support the site which you say is of value to you.Fabian Pascalhttps://www.blogger.com/profile/01346669716885494092noreply@blogger.comtag:blogger.com,1999:blog-6411920579549337139.post-43931590182157241712018-04-16T17:39:33.132-07:002018-04-16T17:39:33.132-07:00IOW, you can designate the SK as PK iff that 1:1 r...IOW, you can designate the SK as PK iff that 1:1 relationship exists.Fabian Pascalhttps://www.blogger.com/profile/01346669716885494092noreply@blogger.comtag:blogger.com,1999:blog-6411920579549337139.post-58451646152489138162018-04-16T17:38:04.082-07:002018-04-16T17:38:04.082-07:00You need to read the paper to which the article re...You need to read the paper to which the article refers -- there is a subtle theoretical reason for the requirement that PKs be names, not properties. But properties can be AKs. So if you don't have a simple name CK and you need to generate a SK, you need to ensure that it has 1:1 value relationship with a guaranteed identifier, which can be the AK. Otherwise the SK cannot ensure entity integrity.<br /><br />That is the case in industry practice (see Part 3), but it should ABSOLUTELY NOT BE! Keys are an element of the logical model and not of physical implementation. You may have to bstardize your model because current systems cannot handle correct models, but you should be conscious of this and not blame the model for the poor implementations.<br /><br />Pls note that generated SKs must be managed by the DBMS transparently to users. They are not the SKs that are generated with SQL systems.Fabian Pascalhttps://www.blogger.com/profile/01346669716885494092noreply@blogger.comtag:blogger.com,1999:blog-6411920579549337139.post-59792316317991588522018-04-16T17:18:56.385-07:002018-04-16T17:18:56.385-07:00When you say: "If there isn't any simple ...When you say: "If there isn't any simple name CK, a surrogate key (SK) should be generated that has a 1:1 value relationship with some CK that represents a name, or set of properties used in the real world to identify entities" - does that not contradict the requirement that PK represent names, and not properties? How does representing a set of properties satisfy the requirement that the PK<br />"Represent names, not properties" and "irreducible"? If the set of properties is namable, then shouldn't they just be your PK?<br />Another question on that is, if a relation is composed of unique tuples, how is it that the full tuple is not itself a CK?<br /><br />It would seem to me that the reasons for using SK over a composite PK is implementation-oriented:<br />- PKs composed of multiple fields that have varying degrees of distinctness can result in poor indexes and poor statistics<br />- A surrogate key, even where there is a perfectly valid CKs available, can aid in composing queries without the user having to investigate what fields compose the PK<br />- In the cases of exceptionally large systems, use of SKs can avoid hitting limits on maximum column counts, etc.<br /><br />Are these valid reasons to favor using SKs over valid CKs in general? Are there other reasons that you might want to avoid using a CK in favor of an SK that are not related to lack of identifying (name) fields?<br /><br />Thanks for your great work on all of these articles, I really appreciate it.<br /><br />Robbadonhttps://www.blogger.com/profile/11031642437447514436noreply@blogger.com