I have a table called contents which contains the ingredients of a specific chemical formula. As suspected, if the ingredients are added to the formula in the wrong order, the formula is not successful. So, consider that I have six fields:
id | formula_id | ingredient_id | quantity | item_id | add_id
Where:
id = the PK and primary index
formula_id = a repeating integer depending on the id of the formula
ingredient_id = the PK from the "ingredients" table
quantity = self-explanatory
item_id = the UNIQUE one-based item id of that ingredient as it pertains to the formula
add_id = the UNIQUE zero-based index of the order in which this ingredient is added to the formula
So, as I am modifying formulas, and adding ingredients, I want to make sure that both the item_id and add_id are incremental integers that are handled by mySQL rather than the PHP code and in a manner that they can be modified later on (should the order of the added ingredients need to be adjusted).
Since I cannot find a decent TRIGGER writing tutorial nor anything about having three AUTO-INC fields, where two only increment based on the "formula_id", I come here and ask for your help.
After some trial and error, I've discovered that it's more my terminology that's incorrect than methodology. What I should have been looking for was a way to create a UNIQUE INDEX based on other fields.
Hence, the solution to my problems is as follows:
ALTER TABLE `chem`.`formulas`
DROP INDEX `item_id`,
DROP INDEX `add_id`,
ADD UNIQUE INDEX `item_id` (`id`, `formula_id`, `ingredient_id`),
ADD UNIQUE INDEX `add_id` (`id`, `formula_id`, `ingredient_id`);