Autoincrement vs composite key for pivot table?

Question

SQL

Can you please tell me in what cases it is necessary to use this or that key and what is the best practice?

Answer 1

1. if there is a Native, natural unique key for the subject of the table, then you need to use it as the primary key.
for example, the TIN or Passport number.
2. A composite key is used in different cases, one of them is when the database is cut into several pieces, then one of the keys of the composite key will be the DBMS Instance.
Another version of the composite, when you need to join 2 tables as M:M (M:1-1:M) - then the link table will have a composite key.
3. Autoincrement is used for the primary key in the case when it is impossible to allocate a natural unique key in the subject of the table. Then technically it is created. You can number the records by software, you can assign to the DBMS.