How can I make a trigger, so that every time a new record is inserted in a table, it updates a field that acts as auto_increment
I have this table
And I want that when inserting a new record in said table, the id field acts as an autoincrement of the previous value, for example if I insert a new record the id field would be 11 and so on, because the PK of that table is the id_user field
I understand that it can be done with a trigger but in MYSQL I don't know how to do it
someone help me please
It doesn't make any sense to want to maintain a kind of
AUTO_INCREMENT
dummy using aTRIGGER
.If what prevents declaring the column
id
asAUTO_INCREMENT
is that itid_usuario
is a primary key, the same is to declareid_usuario
asUNIQUE
, and declareid
asAUTO_INCREMENT
.The definition of the table would be like this:
What you have to ask yourself here is what do I need the column for
id
? do i really need it? It is supposed that, apart from carrying out a sequential control, it is to be used in some cases to order or filter. Well, that's it, the column declared like this is ready and will be indexed so that your queries withORDER BY
orWHERE
that use it are optimized. If you don't need it for any of that, then leave the table as it was, don't make up a column that you won't need.As for
id_usuario
, the constraintUNIQUE
will prevent duplicates from being created, and the column will also remain indexed, for when you need to filter or sort results based on it.final recommendation
If it's a production table, you can modify it with
ALTER TABLE
, but consider saving a copy of it before running theALTER TABLE
.