Clever Geek Handbook

Is there a way to emulate auto_increment on the second column in MySQL?

I think this is clearer than my latest version:

I have a table that already has a PK field starting at 1 already set to auto-increment. I would like to add a new field that does not start with 1, instead starts with an arbitrary number (read the invoice number) and also increments automatically. Apparently you can only have one column per table that has this attribute. Is there a way in a single query operation to force MySQL to increment the last (or maximum) value of this field by 1 as the value of this col on a new row?

Update: I just ended up using the id column as the base for the new value I needed and removed the unneeded column. My question was never answered, but I took it as a hint to rethink the approach, thanks!

+2


source to share


4 answers


This is not really an answer to your specific question (since you are asking how to achieve your goal in one SQL state), but you can define a trigger on the table that populates the second column by calling a sequence generating a sequence function. This will ensure that the value of that column is truly unique (it is not if you do max (value) or the like), since the function calls will be queued.



Or, if you really want two columns that show the same value, create a view that references the same column twice with different aliases.

+1


source


It seems silly to have two columns with the same value, but here you go.



create trigger foo_before_insert 
  before insert on my_table
  for each row set
  new.second_auto_column = first_auto_column
;
+1


source


if in the same table you can set the column you want to use for your unique column but they will have the same value which seems pointless. Are you sure you need this?

0


source


It would be easier to answer if you could tell us what the field is for. There is usually a better way to achieve the same business requirement. Whatever you did, it would be a hack and you ended up wasting a lot of time with unintended consequences.

0


source






More articles:


All Articles