I have a table which is purely used for creating many-to-many relationships, hence all fields are foreign keys of other tables. Should I still include a primary key for this table?
so in short this is the master reference table..if I am getting it correctly. Yes, you can add one mroe column as auto increment primary key which will by default put the clustered index and will make search faster by arranging the data physically. OR you can choose the candidate from exisiting columns for index. For ex. the column which is being referred in most of the case
You should never leave a table without a primary key; at the very least, every table needs a unique constraint. Without one, a table contains only a heap of disorganized and implicitly unreliable data.
How about the number of rows on that table? How frequently that table is used in the queries? See http://knol.google.com/k/sql-server-primary-keys-and-indexes# for reference.
Thanks All. I thought as much. I didn't design this table but stumbled across it and wondered why a PK had been left out. I thought it may have been intentional, given the nature of the data (all FK's for many2many relationships) but probably just oversight. I've added PK. Thanks again.
That explains the reason, also make sure that you have optimized the plan in cache to take the new schema changes in the table.
