Let's say I have a table that is uniquely identified by a Composite Natural Key, but that creating relationships is made easier by creating an AutoIncrement Index to link on...

Is it kosher to use the the CNK as the CPK (to enforce uniqueness without external monitoring code, and b/c logically, that is there PK whether it's implemented that way or not), but still create a Surrogate "Key" that will only be used as a Non-PK handle to the record? It seems a little wierd, b/c any relationships will seem to be FK-to-FK, which isn't logically correct, but I can do it (in Access), and it seems cleaner, if not elegant.

am i missing something?

are you missing anything?

i dunno, but to tell you honestly, i can't see the benefit of what you're doing

if you have a composite natural primary key, and you reference it in other tables, then those have to be composite natural foreign keys

so where exactly would you use the surrogate "handle"? how would you use it? and why?

You don't HAVE to rely on the designation of the natural multipart key as the table's composite primary key to enforce uniqueness. You COULD use a surrogate key as the PK, and set up a multi-part unique constraint instead.

If the only reason for doing this is to save on a couple two- or three-part foreign keys, then I'd question the reason for using the surrogate key a little closer.

If you're trying to avoid a monster 10-part composite key (I ran into Access' 10-part PK limitation once - what a mess...), then using a surrogate key is a good way to go.

If you're PK is likely to change (debate alert ), then use surrogates.

ok, i am constructing a db for a tutoring agency that part of which will handle assignments of tutors to tutees. the complicating factor is that different tutees can require tutoring in diff subjects, potentially requiring diff tutors for each need.

So...I have a ClientNeed table that is uniquely defined by TuteeID x Subject x Level (i.e. college, high school, elementary school) x Date . Now tutors meet tutees in Sessions, uniquely identified by ClientNeedId (the autonumber SK i created) x Date (don't need to worry about >1 tutor meeting a tutee for the same subject at the same time). So, why would i use a SPK on tbl_ClientNeeds, when i can just use a CPK on TuteeID x Subject x Level, and create a (non Primary) SK (ClientNeedID) to link to Sessions on?

I can do this, as i have already set this up, and even if i were to change the values in the CPK that was used as a CFK, i could just cascade updates....

Not trying to be petulant, but a little confused. I have a feeling the answer will be something like, "well, you're using the SK as SPK, even if it's not, so you should convert to reflect that". But then i have to externally enforce uniqueness on combinations of what really are the uniquely identifying elements (TuteeID x Subject x Level).

So, I suppose my question becomes, "Why should the linking element to the foreign key in the Child table (Sessions) HAVE to be the PK of the Parent table as long as it's a unique identifier (i.e., Candidate Key)???"

PS: I had a relationships snapshot in here, but it was huge, if people want to look, let me know, and i'll repost

i am by no means a "standard sql expert" but i'm pretty sure that a foreign key can reference any key in the parent, as long as that key is unique

so if you wanted to, you could have a parent table with a composite primary key, plus a unique surrogate key, and then in the child table, declare a single column foreign key that references the surrogate in the parent

helps?

Edit: hey, where's that diagram go?

yeah, that helps me get stuff done, but I'm still curious about this issue. I suppose the answer is for me to do more research.

Thanks guys.