Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Unanswered: Best Primary Key Solution?

    Hi there,

    Looking for a bit of help with my problem:

    Say i have 3 tables-

    tblClients
    clientID (primary key identity/autonumber)
    clientName (varchar 50)

    tblCities
    cityID (primary key identity/autonumber)
    cityName (varchar 50)

    tblClientsCities
    ID (primary key identity/autonumber)
    clientID (int)
    cityID (int)


    A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.

    I thought is would be easier to make up a unique key for each client with a script eg

    client name: PJ Computers
    Unique key Generated: PJCOMP58784

    Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.

    So my question is what's the best way of accomplishing this?

    Any help would be much appreciated, thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check out @@identity and/or scope_identity. These allow you to work with IDENTITY columns.

    "Smart key" values like you suggested are bad for many reasons. The biggest practical problem is key colisions. The biggest theoretical problem is data changes and how those affect the smart key. There are many other problems, these are just the tip of the iceberg.

    If you want to pursue an avenue a lot like your "smart key" approach that does not have the problems, consider using GUID values using NewId and UniqueIdentifier columns.

    -PatP

Posting Permissions

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