jwcolby
jwcolby at colbyconsulting.com
Wed Jul 25 12:33:43 CDT 2007
Jim, > No I don't. A surrogate key is not a primary key in terms of the model. But I don't work in the model, I work in the real world. A PK is a PK because the database assigns it the PK symbol, and as such I then use it as a pointer. The PK object in any database system (I use) CAN BE a surrogate so the REAL WORLD accepts that surrogates can be PKs. I don't see you calling up MS and complaining that a PK symbol is assigned to your surrogate PK. It may be called a surrogate key, but in the real world it is called it a surrogate PK. > Really? I don't have to have any method of ensuring that each row in a table is unique? Open SQL Server (or Access). Create a table. Create a unique index on it. You now have NO PK but you do have a unique index, ensuring that each row is unique. So SQL Server at least differentiates between PK and unique index. I know that Access works this way. I suspect that the rest do too. A PK and a unique index are NOT the same thing. You MAY specify that a field is the PK, and as such the database will create a unique index - I acknowledge that it has to do that, but again, the index itself does NOT MAKE the PK, the unique index is a necessary attribute of the PK. Being a PK is not an attribute of a unique index. Let's stop discussing the model. It is not useful, I already signed my life away stating that anything you wish to claim about the model is true, so stop with the model already. 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 Jim Dettman Sent: Wednesday, July 25, 2007 12:44 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Primary Key Best Practices John, <<You accept that a PK can be a surrogate, you state that a PK is nothing but a pointer, therefore you accept (and state) that a PK is nothing but a pointer.>> No I don't. A surrogate key is not a primary key in terms of the model. It has no relation to the data what so ever. I agree it's a pointer, but nothing more then that. <<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. >> Really? I don't have to have any method of ensuring that each row in a table is unique? <<And I don't give a rat's patuty about relational theory,>> Yeah, I know you don't as we have been down this road a few times before<g> (something about pig's teats comes to mind). Personally I like to fundamentally understand something as much as possible. It helps me apply things to other areas. <<You are nitpicking to death stuff that simply does not matter. If you want to insist that the PK HAS TO BE a natural key, then how can you say in the next breath that "I don't object to the use of surrogate keys">> Because a "primary key" and a "key" don't mean the same things to me. One denotes something that is connected to the data. The other could be anything. As for using a surrogate key, I don't object to them and I use them all the time. But I think it's important to understand that they are a short cut. In this case, the benefits are many and the ramifications few. Does it matter? In a day to day context no. But in understanding how data can be modeled certainly yes. Jim.