Jim Dettman
jimdettman at verizon.net
Tue Mar 1 14:22:56 CST 2011
Lambert, It needs a unique index, which the Author/Book combination provides. Not sure why the original post got formatted that way, but it should have looked like this: tblBooksAndAuthors LinkID - Autonumber - PK AuthorID - Long - FK to tblAuthors - CK-A BookID - Long - FK to tblBooks - CK-B And not simply: tblBooksAndAuthors AuthorID - Long - FK to tblAuthors - PK-A BookID - Long - FK to tblBooks - PK-B Which might be clearer. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Tuesday, March 01, 2011 01:50 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access and SQL Server Doesn't SQL server require a field with a unique index in order for the table to be updatable? AuthorID cannot be unique (obviously) BookID might not be unique (as some books have multiple authors) So don't you need LinkID - Autonumber PK? Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Tuesday, March 01, 2011 1:40 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access and SQL Server So on a many to many linking table you would do this: tblBooksAndAuthors LinkID - Autonumber - PK AuthorID - Long - FK to tblAuthors - CK-A BookID - Long - FK to tblBooks - CK-B And not simply: tblBooksAndAuthors AuthorID - Long - FK to tblAuthors - PK-A BookID - Long - FK to tblBooks - PK-B and eliminate an index? If so, why not? Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Tuesday, March 01, 2011 12:47 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access and SQL Server When I create a table in any datastore, the first thing I do is create an autoincrement PK. I no longer even think about it - if need a table, I need an autonumber pk! I then proceed to create the fields. John W. Colby www.ColbyConsulting.com On 3/1/2011 12:17 PM, Jim Lawrence wrote: > Many years ago I was taking over an Access project as the clients were > having problems with their invoices. After about two days I discovered > the problem with the invoice. > > It appears that the subform was connected to the main form by grouping > together 3 fields, creating natural foreign key between the two > tables. By some odd set of bad luck certain combinations of this key > hash matched another unrelated key value and the sub form data was > pulling from multiple > invoice details. > > The only reliable solution was to move all the tables to auto PKs but > it cost the client a fair chunk of change. For that reason I have > never inflicted natural keys, on a client, no matter how strong the temptation. > > Jim > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan > Sent: Monday, February 28, 2011 3:00 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Access and SQL Server > > I see a lot of sense in it having a separate Autonumber PK. This is a > classic case of why > you should not use a natural key as your PK. > > What happens when the Description changes and the existing Code is no longer > an accurate > short representation of Description? Do you change it throughout all > the tables which store it or do you leave your customer with strange > Codes which don't match the description > > (And please don't tell me that you use Relationships with "Cascade Update" > turned on.) > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com