Generate new ID for every new combination of column 1 and column 2

Question

I would like to generate a new ID number for every new combination of column 1 and column 2. For example:

ID | column 1 | column 2
1  | peter    | blue
2  | mark     | red
1  | peter    | blue

As there will be new rows added over time, with new values, this should be able to auto-update. I tried DENSE_RANK(), which seemed to work. But it gave an error when I put it as a statement in a calculated column, so I guess this is not possible? (Still very new to SQL).

Thanks for any help!

Error message: #1901 - Function or expression 'dense_rank()' cannot be used in the GENERATED ALWAYS AS clause of `ProductIdentifier

EDIT: What I basically want is to link a row to another table based on the 2 columns. I could also concatenate the two columns of course, but I read somewhere that doing this with string will be slower. It will ultimately be a big table with currently 200.000+ rows and growing to millions. Is this something I could/should do?

Answer 1

I don't understand. If you want column1/column2 to be unique, then they should be in their own table:

create table t12 (
    t12_id int auto_increment primary key,
    column1 int,
    column2 int,
    unique (column1, column2)
);

This gives you the unique value for the pair that you seem to want.

Answer 2

You can't use window functions in a generated column, as you found out. You can, however, compute the information on the fly in a view:

create view myview as
select id, column1, column2, dense_rank() over(order by column1, column2) rn
from mytable

Then you can query the view instead of the table, like:

select * from myview;