0

I want to know how can I add more than one auto increment in the same table. Usually I use sequence for this issue but in myphpadmin, I don't have sequence so I have do it in other way.

Below is the example I'm trying to achieve. which is have ID, SecondID and thirdID all auto increment. I cant change the structure to have only one auto increment due to many issues so i need to follow this structure

CREATE TABLE test (
  ID              INT    NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  section  VARCHAR(300) NOT NULL, 
  secondID           INT   AUTO_INCREMENT,
  thirdID           INT   AUTO_INCREMENT,
  message  VARCHAR(1000) NOT NULL
);

CREATE TRIGGER testing After INSERT
ON test
FOR EACH ROW
INSERT INTO test(secondID) VALUES(LAST_INSERT_ID());
END;

I tried using the Trigger but im getting error saying

"#1442 - Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. "

Can anyone provide me with suggestion or approach to solve this issue

Thank you

new_dev
  • 1
  • 1
  • I can't imagine how/why secondid and thirdid values could differ from id so why not make them the same using a trigger or generated column. -or perhaps the published table is not representative? – P.Salmon Oct 26 '22 at 07:31
  • Unsolvable. You cannot have more than one AUTO_INCREMENT column in the table. From the other side I strongly doubt that you really need in AUTO_INCREMENT for these columns. Post the task itself, describe why you decide that you need in AI for these secondary columns - I think that your logic is not correct, and the task must be solved by another way. XY-problem. – Akina Oct 26 '22 at 07:59
  • @P.Salmon Neither trigger nor generated column can help. BEFORE trigger - AI value not exists yet. AFTER trigger - AI accessible, but the trigger cannot update the table. Generated column - cannot refer to AI column. – Akina Oct 26 '22 at 08:00
  • Im planning to use Trigger but if you can guide me how to do it it will be great – new_dev Oct 26 '22 at 08:19
  • Does this answer your question? [How to create two auto increment columns in MySQL?](https://stackoverflow.com/questions/22824439/how-to-create-two-auto-increment-columns-in-mysql) – Rohit Gupta Oct 26 '22 at 10:26

1 Answers1

0

There can be only one AUTO_INCREMENT column per table...

This is how MySQL is implemented. You may want to look into creating triggers if you absolutely need this. Another option is incrementing it from your application logic.

Mango Pickle
  • 24
  • 3