[AccessD] Access and SQL Server

Hi Asger et al

So true. Had to maintain a system with up to five-field compound PKs. Terrible.

/gustav


>>> ab-mi at post3.tele.dk 04-03-2011 00:45 >>>
Jim,
Coming in on this discussion late and having read the whole posting so far I want to take up a point way back:
Why impose the overhead of using a surrogate PK in a linking table instead of just using a composite natural PK?
JC has clearly stated that using a surrogate PK key doesn't mean that you can omit a unique index on some other "natural column" or combination of "natural columns" in your table (which is also called "alternate keys").
So in your example, even if I use a surrogate PK I also need a natural unique index of the combination AuthorID and BookID.
I think we all can agree on this. I also think we all can agree that the surrogate PK imposes an overhead compared to just using the composite natural key as a PK. But what happens if you need to create a child table to this table?
Then the story is quite different: using a surrogate PK in the main table you only need a FK with a single column in the child table - using a natural composite PK in the main table you need a FK with as many columns as used in the main table. And this certainly imposes a much bigger overhead. Also to get a good performance you normally will create indexes on the FK. So having a composite FK will impose even more overhead. Not to mention that if you need one child table then chances are that you might need two or more child tables - each one imposing an overhead as compared to using a surrogate key with one column.
That's why I always use surrogate PK's - even in a linking table which *for the moment* doesn't seem to need child tables.

Asger