I have two tables, products
and categories
which have a many to many relationship, so I'm adding a products_categories
table which will contain category_id
and product_id
.
Should I add another (auto-incrementing) index column or use the two existing ones as primary key?
You don't need to add an extra, auto-incrementing index column, but I (perhaps contrary to most others) still recommend that you do. First, it is easier in the application program to refer to a row using a single number, for example when you delete a row. Second, it sometimes turns out to be useful to be able to know the order in which the rows were added.
No, it's not necessary at all, given that these two columns are already executing the function of a primary key.
This third column whould just add more space to your table.
But... You could use it maybe to see the order in which your records where added to your table. That's the only function I can see to this column.
You don't need to add an auto-incrementing index column. Standard practice is to use just the two existing columns as your primary key for M:M association tables like you describe.
I would make the primary key category_id and product_id. Add an auto increment only if the order will ever be relevent in later uses.
There's a conceptual question - is products_categories an entity or is simply a table that represents a relationship between two entities? If it's an entity then, even if there are no additional attributes, I'd advocate for a separate ID column for the entity. If it's a relationship, if there are additional attributes (say, begin_date, end_date or something like that), I'd advocate to have a multi-column primary key.