Introducing surrogate key in may-to-many table? I have 3 table:

brand: brand_id(pk), name

product: product_id(pk), name

brand_product: brand_product_id(pk), brand_id, product_id

And I have a report table, at the moment it looks like this:

product_report: product_report_id(pk), quantity, brand_id, product_id

My question is, what are the pros and cons of having the brand_product_id column instead of brand_id, product_id.
I would suggest that brand_product_id is redundant.
Yes, every table row must be uniquely identifiable.
Nobody ever said that had to be accomplished with a single field.
Logically, this is a &quot;Weak Entity&quot;.  No record can exist in this table without corresponding entries in the other two tables.
It is highly unlikely that you would ever want to query this table without reference to one [or both] of the other two tables, i.e. you would always want to query it for a brand or a product.   Therefore, the [unique] combination of brand_id and product_id is sufficient for a Primary Key.
I would suggest that your biggest decision (if it's a decision at all) is whether to have the Primary Key on (brand_id, product_id) with an index on (product_id, brand_id) for the &quot;reverse&quot; look-ups, or the other way around.  YMMV.