Results 1 to 8 of 8

Thread: Primary Key

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Primary Key

    I need to create my own primary key, how do I go about doing that?? In the database I am working in usually has a primary key that looks like this VL0008
    the V is for Vendors, thats basically their number. Some of these Vendors need to be licensed and some dont, the ones that are not licensed dont get a number but I am to use that as the Primary/Index key I need to create one for those particual vendors. How can I go about doing that??? I was wanting to make it TL888 something like that.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "my own primary key" mean?

    the table either has a primary key or it doesn't

    if it doesn't, just create one

    if it does, you have to remove that constraint before you define another column as the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm more than a little confused. A primary key can be any datatype that supports enough unique values for each row of interest to have exactly one value for the primary key. Most of the folks here like to use integers, although everybody is free to use their own preference (distributed apps like GUIDs, most legacy apps seem to like CHAR, whatever works...).

    I'd suggest picking the whatever datatype suits you, and simply using it. I would avoid anything that needs specific formatting, these often cause more problems than they solve. Most of the folks here like to use an int with an IDENTITY attribute.

    To add a PK value to table BigFatMess, you could use something like:
    Code:
    ALTER TABLE BigFatMess
       ADD BigFatMessId INT IDENTITY (-2147483648, 1)
          CONSTRAINT XPKBigFatMess
             PRIMARY KEY (BigFatMessId)
    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think she is looking for a way to auto-generate alphanumeric keys (which could then be used as a primary key...).

    You could try creating an incrementing identity field in your table to create the numeric portion and then have a calculated field that prepends your alphabetic string.

    But you should think about WHY you want this and WHAT you are going to do with it. Frankly, a code such as "TL888" is not as usefull as two separate columns for storing the "TL" and "888" portions, because as a single value you will need to apply character functions to it to parse out and use its components and you will lose the benefit of indexes.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Yes thats it

    Yes Blindman thats exactly what I am trying to do thank you

    I'm sorry guys I'm doing a poor job of explaining myself.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So is your issue resolved?
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    I think she is looking for a way to auto-generate alphanumeric keys (which could then be used as a primary key...).
    And why wouldn't a composite key do the same thing?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    thanks guys

    I have found another solution, thanks for your help though I appreciate it

Posting Permissions

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