Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Auto Increment on Non PK column

    How can I put an Auto increment on a non PK field?
    for ordering contents items?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You just create an identity field..
    Code:
    CREATE TABLE dbf (
       some_field char(10) PRIMARY KEY
     , identity_field int identity(1,1)
    )
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    What If I already have a Identity field in the table as the PK, which is need as a Foreign key on a Join within the table? I know I could build a trigger to do max(field), but this might be slow, not sure, am I able to have two identity fields in the table?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why would you need two different identity columns?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and now that I think about, why would you ever want to order items by an identity?
    Your business requirements sound totally f'd up.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    how about a calculated column which is just equal to the PK identity column.


  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    I know I could build a trigger to do max(field), but this might be slow, not sure, am I able to have two identity fields in the table?
    I did this once and I think the trigger pulled it from the PK's identity field, but in that case they were functionally related. Or maybe it was procedural code... Another option is to create a dummy table with its own identity field, and just immediately delete the useless rows. It's silly, but it'll work.

    ...and now that I think about, why would you ever want to order items by an identity?
    Your business requirements sound totally f'd up.
    I include myself in this indictment because I've done it plenty of times and I was initially going to say "get Oracle and use sequences": Why do we answer a "how do I do X with Y" with "X is a stupid idea" or "don't use Y, use Z"? I mean, we're engineers here, why can't we give a body a straight answer?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't have two identites on a single table since identity is a table property. I too would ask why you need a second one - why not use the PK? Perhaps some DDL, sample data and expected return might help us understand what you are trying to do - there might be a better way.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sco08y
    Why do we answer a "how do I do X with Y" with "X is a stupid idea" or "don't use Y, use Z"? I mean, we're engineers here, why can't we give a body a straight answer?
    Well the straight answer is "you can't do X with Y. Next!". We need to probe requirements to see if we can come up with something that will meet these.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by pootle flump
    Well the straight answer is "you can't do X with Y. Next!". We need to probe requirements to see if we can come up with something that will meet these.
    That's very true, given that a timely negative can save a lot of fruitless searching. I think the dummy table technique should do what he wants, though.

Posting Permissions

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