Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Using Surrogate Autonumbers as Handles to a CPK

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Re: Using Surrogate Autonumbers as Handles to a CPK

    Originally posted by pokeynyc
    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"...
    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.
    Bradley

  4. #4
    Join Date
    Jan 2004
    Posts
    4

    here's what i'm talking about

    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
    Last edited by pokeynyc; 01-19-04 at 01:29.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •