Foreign Key Index question

Transact-SQL
1

Hi, Let's say you have a Client table and a Staff table with a relationship table ClientStaff that has a foreign key to Client and a foreign key to Staff with a uniqueness constraint on Client, Staff. Meaning a client can have multiple staff associated but never the same staff more than once and vice versa. The question is would it be beneficial to have indexes on the Client and the Staff foreign keys in the ClientStaff table or do you think the index created by the uniqueness constraint is enough? Queries will be along the lines of SELECT * FROM Clients c JOIN ClientStaff cs ON c.id = cs.Client JOIN Staff s ON s.id = cs.Staff

Thanks

2

Yes the foreign key should have a supporting index starting with the column.

3

No, you don't need additional index(es) on the ClientStaff table. The Client and Staff values will not be looked up there, only back in their original tables.

The Client table needs an index on c.id, and the Staff table on c.id, but I'm virtually certain you already have those in place.

Excellent design by the way! Most people just mistakenly slap an identity column on these "intersection" tables, and that's awful, it's much better to use the natural keys combined, as you're doing here.

4

Thanks for the responses!.