I've a fair number of tables in my various databases, which are there for the sole purpose of accommodating many-to-many associations. The tables are very simple - they contain only two columns, namely, the ID number of the primary key(s) in one table and the ID number of the primary key(s) in the other table. EG:
Technically speaking, one column is the "owner" of the other column, such as, for example, one column might be a Company ID while the other is a Contact ID. However, I've never actually designed a table such that the Primary Key of the table was something other than an Identity field.
So, my question is simple: Which of the following cases is ideal?
1. Leave the table as-is. Both columns are Foreign Keys, and in some cases, two indexes exist, with each index having one column being the first and the other column being the second.
2. Modify the table, and make PK_1 the Primary Key of the table. Also, in some cases, an index would need to exist for the other column. A question though, in that case - can a Primary Key also be a Foreign Key?
3. Modify the table, and add a new column, which is an identity column, and make that field the Primary Key. As in case 1, this would also entail situations where we would need two indexes.