Generate auto-increment values in a composite primary key not first field in the innoDB table


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
6 views
The essence of the problem was that MyISAM tables have this ability and not for innoDB.

There is a table

messages
user_id INT
message_id INT


How to make so that when you insert into a table the values of the user_id, the value for message_id is generated
automatically?

Should be something like this
user_idmessage_id
11
12
13
21
22
23


Made a trigger before insert which sets the value of the message_id as maximally id + 1,
everything works as it should, but after the insertion I do not know what message_id recorded the trigger.

The option of calculating the message_id and then insert (in two queries) is not suitable.
by | 6 views

1 Answer

0 like 0 dislike
I'd say no need to do so. message_id should be unique and auto-increment.
But if You want to write beautiful numbering for each individual user, make a separate field `number` SAMLLINT — it and figure out the trigger.
by

Related questions

110,608 questions
257,186 answers
0 comments
28,075 users