Auto increment based on two columns?

Databases
1

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:


CREATE TABLE shipments
(
	deptId BIGINT UNSIGNED,
	parcelId BIGINT UNSIGNED,
	PRIMARY KEY(deptId,parcelId)
)

Example data:

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

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

sure, glad to oblige

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

works only for myisam tables

3

Gah! I’d missed that part of the manual! That does the trick (shame it doesn’t work on INNODB tables, though).
Thanks