How to Generate a Sequence Column Based on a Foreign Key in MySQL?

MySQL: Generating Sequence Column Based on Foreign Key

In a legacy database, adding a sequence column that records a numerical order for each foreign key can enhance data retrieval and organization. Suppose you have the following table:

ID	ACCOUNT	some_other_stuff
1	1	...
2	1	...
3	1	...
4	2	...
5	2	...
6	1	...

The goal is to create a SEQ column that increments separately for each ACCOUNT. This would result in the following table:

ID	ACCOUNT	SEQ	some_other_stuff
1	1	1	...
2	1	2	...
3	1	3	...
4	2	1	...
5	2	2	...
6	1	4	...

To achieve this, you can utilize a SQL trigger:

CREATE TRIGGER trg_mytable_bi
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
      DECLARE nseq INT;
      SELECT  COALESCE(MAX(seq), 0) + 1
      INTO    nseq
      FROM    mytable
      WHERE   account = NEW.account;
      SET NEW.seq = nseq;
END;

This trigger ensures that every new row inserted into the mytable table gets a unique sequence number based on the related ACCOUNT.

The above is the detailed content of How to Generate a Sequence Column Based on a Foreign Key in MySQL?. For more information, please follow other related articles on the PHP Chinese website!