Jim Dettman
jimdettman at verizon.net
Thu Mar 3 09:02:40 CST 2011
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. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, March 02, 2011 06:04 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access and SQL Server 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*. Candidate keys perform function #1 above. There may be several candidate keys. They cannot all be the PK because there can be only one PK! Whether you use a candidate key or a surrogate key as your PK, selecting it as you PK means it CAN AND IN FACT does both. So a PK is a PK simply because it can do both 1 and 2 above AND you select it to be your PK. It does *not* have to be used as a FK to be a PK. However IF you need a FK in a child table to link back to the parent table, then you are *supposed to* use the PK. If you are a fruit or nut, you could use any candidate key to do that. You could even use one candidate key as the FK in table ABC and a second candidate key as your PK in table XYZ. You would of course be a fruit or nut to do that. In fact (IMHO) you would be a fruit or nut to use a natural key at all. ;) An autonumber field with a unique index on it is automatically a candidate key. It can be used to uniquely identify any given record in the table - #1 above. However it in no way guarantees the uniqueness of the *data* in the table. Only a unique index covering a set of fields which, taken together guarantees unique data, will in fact enforce data uniqueness. A natural PK, by definition, performs both. By making it the PK, SQL Server automatically creates a unique index on the entire set of fields, and guarantees *data* uniqueness and, while it is at it, calls it the PK. If we are going to use a surrogate PK, then we *still* have to perform the analysis to find at least one candidate key, and we have to manually create a unique index to cover that candidate key (set of fields). John W. Colby www.ColbyConsulting.com On 3/2/2011 4:47 PM, Jim Dettman wrote: > John, > > << > << Having an auto number PK does not in any way relieve the developer from > the responsibility of > analyzing for a field or set of fields which ensure uniqueness and setting a > unique index on those > fields.>> > > >Yes because it's a misnomer to call a auto number a PK or even a surrogate > key. It may be labeled > as such, but it certainly does not perform the function of one. > > What? It certainly can. >>> > > What then is your definition of a PK? > > Jim. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com