SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    518
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Auto increment based on two columns?

    This might be simple and I'm just having a mental day, but here goes...

    I'm trying to accommodate an incremental value in column B, for each unique value of column A. That is, an incremental parcel number for each department:
    Code:
    CREATE TABLE shipments
    (
    	deptId BIGINT UNSIGNED,
    	parcelId BIGINT UNSIGNED,
    	PRIMARY KEY(deptId,parcelId)
    )
    Example data:
    Code:
    deptId  parcelId  
    ------  ----------
         1           1
         1           2
         1           3
         2           1
         2           2
         2           3
    Obviously a normal auto_increment won't work.

    I can manually calculate the next available parcel number for a given deptId, when I insert, eg
    Code:
    INSERT INTO shipments(deptId,parcelId)
    SELECT 1,IFNULL(MAX(parcelId),0)+1
    FROM shipments WHERE deptId=1
    However, this wouldn't return my new parcelId (which I'd really like it to do, for my application layer). I could do a separate SELECT to get the next available parcelId, and then do an INSERT, but then there's the (slight, but real) possibility of a race condition. So I can't really think of a solution that would accomplish the following:
    - Maintain an incremental value in column B for a specified value in column A
    - Upon insert, return the new value of column B
    - Avoid race conditions

    Hopefully someone will point out the blindingly obvious solution that I've missed...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    Hopefully someone will point out the blindingly obvious solution that I've missed...
    sure, glad to oblige

    please read the manual here -- http://dev.mysql.com/doc/refman/5.5/...increment.html

    works only for myisam tables
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    518
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Gah! I'd missed that part of the manual! That does the trick (shame it doesn't work on INNODB tables, though).
    Thanks


Bookmarks

Posting Permissions

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