Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Oct 2003
    Posts
    84

    Question Unanswered: Dependant IDENTITY columns

    Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.

    For exameple:
    Let's say I have a client table with a ClientID column as it's PRIMARY KEY.
    This column can be an auto-incrementing IDENTITY column.

    Then I have an orders table. The PRIMARY KEY for the orders table is composed of (ClientID, OrderID). I would like the OrderID to be an IDENTITY field that increments by an arbitrary value (1 in this case) for every new value of ClientID...therefore creating a unique PRIMARY KEY.

    The contents of the table would look like this

    ClientID OrderID
    ------- -------
    1 0
    1 1
    2 0
    1 2
    2 1
    2 2
    1 3

    and so on....

    MySQL (and maybe other RDMS's...I haven't checked) seems to do this automatically when you set a column as AUTOINCREMENT and then define a composite PRIMARY KEY on two fields.
    I know this can be done manually using triggers, but I was wondering if there was a better way...

    Thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You are only allowed 1 IDENTITY Column per table...

    How do you plan to INSERT the data in to the table?
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    MySQL will do that? I'm skeptical...

    Yes, you can do this using triggers, but it seems as if you are going to be using these values and their order as part of your application logic. That's generally not a good idea. An autoincrementing ID is a surrogate key, and should not have any inherent relationship to the data it represents.

    What are you planning to use these values for?
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Oct 2003
    Posts
    84

    Smile

    I know.


    In an INSERT into the Orders table, I would supply the ClientID (and all the other fields I didn't mention in the orginal post) and the OrderID "for that client" would be incremented. Maybe you thought I meant that in the orders table the ClientID and OrderID are IDENTITY columns...

  5. #5
    Join Date
    Oct 2003
    Posts
    84
    BlindMan:
    I wasn't planning on using it for anything (for now anyways), I was just curious. The Clients/Orders table was just a commonly used example.

    As for it being possible with MySQL, I checked it again (since I first discovered this a while back), and yes it is possible. The syntax under MySQL would roughly be:

    CREATE TABLE Clients (
    ClientID INT AUTO_INCREMENT PRIMARY KEY);

    CREATE TABLE Orders (
    ClientID INT,
    OrderID INT AUTO_INCREMENT,
    CONSTRAINT PK_SomeName PRIMARY KEY (ClientID, OrderID));

    Obviously there would be a foreign key set on Orders.ClientID....but I just want to show the example..

    After populating Clients with a few values, you can insert a few into orders
    like so:

    INSERT INTO Orders (ClientID) VALUES (SomeValues);

    and the OrderID field will increment like I mentionned in the original post,
    except that the default AUTO_INCREMENT seed is "1", not "0".

    Like I said, I was just curious if it was possible to use this with MS-SQL
    without using a workaround.

    Thanks for the information!!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    MySQL will do that? I'm skeptical...
    oh ye of little faith, yes, it certainly can (as afx2029 so nicely illustrated)

    mysql can do several things that make sense when you see them

    other databases could learn from some of what they're doing

    and before y'all jump all over me, i did not say mysql was better than other databases, just that they actually do some neat things

    i'm still undecided about whether i like the ability to store 2004-05-00 and 2002-00-00 as perfectly valid datetime values (there's a good reason for it), but i am aghast at them allowing 2003-02-29 and 2001-02-31
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2004
    Location
    LA, CA
    Posts
    125
    Rudy, what would be a good reason for allowing (2002-00-00) or (2004-05-00 ) as valid datetime?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah Rudy! If you love MySQL so much, why don't you just MARRY it? Huh? Well, why dontcha? If it's so much BETTER?
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    GDMI, to allow you to use a single date column to record an event (birthday, battle, etc.) and be able to store partial information, e.g. if all you know about great-great-grand-uncle Fritz is that he was born in 1903 but you don't know the month or year, put 1903-00-00

    you can't do that with a "normal" date column in other databases, which require an exact date, so you'd either have to carry separate year, month, day columns and allow nulls, or else fake it by putting 1903-01-01 (which, i hasten to point out, is wrong and misleading)

    blindman, i've sworn off marriage, having gone through two of them

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2004
    Location
    LA, CA
    Posts
    125
    Rudy, Thanks for the explanation. I admit I haven't thought about that before ...

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by GDMI.
    Rudy, Thanks for the explanation. I admit I haven't thought about that before ...
    I can't tell. Is he talking about your code or your marriages? I quit after one, so I guess that makes me a more efficient marriager than you.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    I can't tell. Is he talking about your code or your marriages? I quit after one, so I guess that makes me a more efficient marriager than you.
    Everybody should do it at least once. It helps promote the species, and it instills a real fear of the potential of hell.

    This coming from a guy who's never found an eligible woman smart enough to be interesting and dumb enough to say "yes".

    -PatP

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hell hath no fury like a woman with a divorce lawyer

    but it is worth the pain

    i have two fantastic boys from my first marriage, 32, and 30

    and a fabulous daughter, 13, and son, 11, from my second

    i would love to have even more kids, but i am broke, out of work, and too old and unattractive to have any hope of a third marriage

    my only hope now is that one of my older boys has grandkids soon
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2004
    Location
    LA, CA
    Posts
    125
    Quote Originally Posted by blindman
    I can't tell. Is he talking about your code or your marriages?
    Nope! I was talking about his code!

    But speaking about marriages .. I am preparing for my marriage .. hopefully it will be my first and last eventhough statistics are against that.

    Blindman, Rudy you guys are great SQL consultants in a "SQL World" that alone should qualify to get chicks!

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Careful, GDMI. Statistics shouw that just over half of the marriages in the US end in divorce. But just under half end in death!

Posting Permissions

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