2 auto_increment fields where 1 resets

Question

I'm about to code a couple of MySQL tables to handle invoices.

My plan is to break this into 3 main tables:

create table invoice
(
  id auto_increment,
  client (foreign key),
  created (date),
  *etc*...
)

create table products
(
  id auto_increment
  *product info*...
)

create table invoice_products
(
  invoice_id (references invoice.id)
  row (resetting auto_increment)  <--THIS!!!
  product_id(references products.id)
  product_quantity INT
  primary key (invoice_id,row)
)

The dilemma is, that when a new invoice is created, invoice.id is auto_incremented, and this is as it's supposed to be. What I wan't is for the invoice_products.row to start from 1 for every new invoice. So the row auto_increment will start from 1 for every new invoice, but if new rows are added to an existing invoice id, the row id will continue from where it left off.

Any recommendations on how to accomplish this?

(I hope the short version of the code is enough for you to understand the dilemma)

Thanks in advance for any advice!

EDIT: Clarification: All tables in the database are InnoDB (because of heavy use of foreign key constraints)

Answer 1

All you have to do, is to change your table to using MyISAM engine. But note, that MyISAM doesn't support transactions and foreign keys.

Anyway, here's an example how it would work (quoting the manual):

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

As you can see, your primary key and auto_increment setup is right already. Just change to MyISAM.

---

If you don't want to use MyISAM, you can also calculate it while selecting.

SELECT
ip.*,
@row := IF(@prev_inv != invoice_id, 1, @row + 1) AS `row`,
@prev_inv := invoice_id
FROM invoice_products ip
, (SELECT @row:=1, @prev_inv:=NULL) vars
ORDER BY invoice_id

And third possibility is of course to calculate it outside the database. That I'll leave to you :)