Mysql Second Auto Increment Field Based On Foreign Key

I've come across this problem numerous times but haven't found a 'MySQL way' to solve the issue as such - I have a database that contains users and reports. Each report has an id w

Solution 1:

MyISAM supports the second column with auto increment, but InnoDB doesn't.

For InnoDB you might create a trigger BEFORE INSERT to get the max value of the reportid and add one to the value.

DELIMITER $$
CREATETRIGGER report_trigger
BEFORE INSERTON reports
FOREACHROWBEGINSET NEW.`report_id` = (SELECTMAX(report_id) +1FROM reports WHERE user_id = NEW.user_id);
END $$
DELIMITER ;

If you can use MyISAM instead, in the documentation of MySQL page there is an example:

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

CREATETABLE animals (
    grp ENUM('fish','mammal','bird') NOTNULL,
    id MEDIUMINT NOTNULL AUTO_INCREMENT,
    name CHAR(30) NOTNULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

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

SELECT*FROM animals ORDERBY 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 |
+--------+----+---------+

Solution 2:

Right one with IFNULL:

DELIMITER $$
CREATETRIGGER salons_trigger
BEFORE INSERTON salon
FOREACHROWBEGINSET NEW.salon_id = IFNULL((SELECTMAX(salon_id) +1FROM salon WHERE owner = NEW.owner), 1);
END $$
DELIMITER ;

Solution 3:

I think mysql doesnt support two auto_increment columns. you can create report number using information schema.

selectNULLfrom information_schema.columns  

Solution 4:

MySQl does not support two auto incremented fields, if you need then create another table, set the other field which you want to be as auto incremented and you must set up a relationship with these two tables.