jwcolby
jwcolby at colbyconsulting.com
Wed Jul 25 16:45:54 CDT 2007
>Try updating a SQL Server table linked to MS Access that does not have a primary key. It will not let you make any change to it at all. True, but it does NOT require one for the table to exist in SQL Server. I am simply making a point that the PK is not the unique index. The PK HAS ONE unique index, and it has to have a unique index to be labeled as the PK (by the database engine) but it is NOT JUST A UNIQUE INDEX, it is a pointer to a record that, oh by the way, happens to have a unique index. You can create a table in SQL Server and not label any field or combinations of fields as the PK inside of SQL Server. Make that field an auto increment. DO NOT place a unique index on it. Create 1 million records. Now go link that table to Access and TELL ACCESS that the autonumber field is the PK. Now you can do the deletes and updates. IT IS JUST A POINTER. It does NOT have a unique index in SQL Server, and it is not LABELED as the PK in SQL Server. But the field still (correctly) points to a single record back in SQL Server and can be used to find and delete a record. Now, go into that table in SQL Server and assign some natural key as the PK. Go back to Access. Even though you have not told ACCESS that the natural key is the PK, you can STILL delete a specific record out in SQL Server using the original autonumber. The field is JUST A POINTER. Now go into SQL Server and find a second (and third and fourth) CANDIDATE KEYS and create unique indexes on them. You haven't in any fashion made those candidate keys the PK, all you have done is set up a new unique index. Call it what you want, I don't care. A unique index is NOT a PK and a PK is NOT a unique index. Different things entirely. The FIELD or FIELDS that have a unique index applied to them may be chosen as the PK, but the index itself is NOT THE PK, and the PK IS NOT THE UNIQUE INDEX. The field itself (with or without an index) is the PK, and the contents of the field are used for the lookup to find the matching records in other tables. The unique index does nothing more or less than enforce uniqueness, which of course is a requirement of the CONTENTS of the field(s) that make up the PK, and the unique index is thus FORCED to exist by the database engine (correctly so). Now quote the model all you want, as I said, the model is not of interest to me, reality is of interest to me. Models do exactly that, MODEL REALITY, they are not the complete representation of reality. If they were they would cease to be a model and become the real thing. The model is interesting but it is not complete, and it is being very effectively used to obscure the issues in this discussion. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart Sent: Wednesday, July 25, 2007 3:31 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Primary Key Best Practices At 12:00 PM 7/25/2007, you wrote: >Date: Wed, 25 Jul 2007 11:19:00 -0400 >From: "jwcolby" <jwcolby at colbyconsulting.com> >Subject: Re: [AccessD] Primary Key Best Practices >To: "'Access Developers discussion and problem solving'" > <accessd at databaseadvisors.com> >Message-ID: <20070725151912.941B1BCEA at smtp-auth.no-ip.com> >Content-Type: text/plain; charset="us-ascii" Whooo, there cowboy. Without a PK, how are you going to identify rows for updates and delets? Good design REQUIRES a primary key of some kind even if it is a composite key. Try updating a SQL Server table linked to MS Access that does not have a primary key. It will not let you make any change to it at all. >AND... from the perspective of the child, a FK is nothing but a pointer. >Does a table HAVE TO HAVE a PK at all? Not if it has no children. >Ergo, what matters from the child's perspective is what the PK is all >about, and that is simply a POINTER back to the parent. AND... Since a >surrogate key functions as a PK, then... A PK must be nothing but a pointer... -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com