Composite primary key and additional indexes
sql-server, sql-server-2005
Solution
Make the composite primary key (parent_id, child_id) to enforce uniqueness. SQL Server can use the composite for searches on both columns or parent_id only, but it cannot use it for searches on child_id only. If you need it, a separate index on child_id would have to be created.
Problem
In SQL Server 2005, I have a table with two columns: `parent_id (int)` and `child id (int)`. I want make a composite key of them, because I only want exactly one instance per possible combination in the table. The most search operations will be done on the `parent_id` field, some on the `child_id` and only sporadic ones on both fields together. I have planned to make an index on the `parent_id` field and maybe also one on the `child_id` field. Is this meaningful or is SQL Server 2005 capable of using the clustered composite primary key for indexed lookups on only one column (mostly the `parent_id`) and therefore the index is not necessary/dispensable?