Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    1

    Question Primary Key Issues

    I am creating a database that by it's nature will be used locally and syncronised with a central copy whenever possible.

    To retain integrity Primary Keys will be generated by auto-incremented added to the username of the person adding the record, my problem is how to store this.

    Using 2 Fields seems wasteful or untidy. The alternative that I can see would be to combine them into a single field, this again doesn't seem to be a very nice way of doing things.

    I'm guessing that I am being pedantic, and that creating a single field would be the best option.

    Any thoughts?

    Cheers
    Matt

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If you are concerned about keeping the IDs unique across distributed databases, use a GUID as a primary key instead of an integer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you stay with the username plus autonumber, keep them as separate fields
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Note that an autoincrement/sequence/identity field is unique, by itself. The name field would not be required for uniqueness, and is thus redundant to be included as a part of the PK.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, but loquin, what if the database will be "syncronised with a central copy whenever possible"

    if todd creates rows 1, 3, and 5 in his local database, and mary creates rows 2, 4, and 5 in her local database, what happens when these rows are brought into the central database?

    hence the need for an additional column in the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by r937
    yes, but loquin, what if the database will be "syncronised with a central copy whenever possible"

    if todd creates rows 1, 3, and 5 in his local database, and mary creates rows 2, 4, and 5 in her local database, what happens when these rows are brought into the central database?

    hence the need for an additional column in the primary key
    D'oh! missed that.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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