Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: AutoIncrement Function

    Hi

    I have a table where an ID is defined as PK with auto-increment.
    In the same table, I have ID2 (as FK) and an ID3.
    I need to increment ID3 by one for every ID2.
    Something like this
    Code:
    INSERT INTO `tbl`
    SET ID2 = '5' AUTO_INCREMENT `ID3`
    Is this possible ?

    Thanx
    MySQL 5.1

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    This should be possible in a trigger.

    But the main question is why you would want to do this? The PK makes the records unique, adding ID3 makes an alternate key which adds no value to the record (it's just another technical key), it's redundant data.

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    ID is PK and (ID2,ID3) is unique.

    ID2 is used for reference to another table, and ID3 is required to be auto-incremented for every ID2 inserted.

    If I can auto-increment for every ID2 inserted then, I can save on PHP code to do this.

    Btw, for internal db programming, is Oracle the better choice ? I understand you write programs for your database within Oracle. I've to check with my host to provide the free version of Oracle.
    MySQL 5.1

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As you are using MySQL V5 then you should be able to make use of MySQL's triggers and stored procedures to do this..
    ...got to agree with Lexiflex.. it does seem a very peculiar user requirement
    if ID2 is incremented then ID3 should also be incremented and noth are a composite FK.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    ID2 gets incremented in its own table for which its PK but in this table (where ID1 is PK), ID2 can be any number.

    But for every ID2 inserted in this table, ID3 has to be incremented. I want this sequence.
    MySQL 5.1

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    Have you looked at the manual? There is an example which might be what you need to adapt.

    http://dev.mysql.com/doc/refman/5.0/...increment.html


    see the grp, animal, name example

Posting Permissions

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