I've been re-working some of my table design ideas recently, and have been attempting to learn as much as I can about Indexes and whatnot so as to have the database be as fully functional and efficient as possible. A question has come up in my mind, however, regarding the differences between Constraints and Indexes (indices?). Here's an example of my situation:
SubjectVisit - table
SubjectVisitID - Surrogate PK, Autonumber INT Identity field
SubjectNumber - FK to Subject table where it is unique INT arbitraty for each subject
VisitDate - DateTime
This table is going to be used to track when a subject visits the research laboratory that I work for. Each subject can only visit once per day, but may visit on many different days, so I want to have a Unique Constraint which will limit based on that logic. I also want to have a Clustered Index on SubjectNumber and would like for VisitDate to have an Index as well since it will be used in the WHERE of many queries, and of course SubjectVisitID will be the PK.
Here are the indexes/constraints that I was thinking would be necessary assuming that a Unique Constraint does not actually act as an Index itself:
PK on SubjectVisitID - In the .ADP I'm working with which is linked to a SQL Server backend this looks like it is simply a Unique Constraint...is this an Index as well?
Unique Constraint on SubjectNumber, VisitDate - For the purposes of business logic, but is this also an Index?
Clustered Index on SubjectNumber - To physically order the data in the most useful fashion.
Index on VisitDate - VisitDate will be searched against often as a criterion. Hopefully this index will be selective enough to be useful.
So my questions are:
I've also read that you should never have more then one index on a field. Does having the Unique Constraint and the Clustered Index on SubjectNumber break this rule? How exactly are Constraints regarded by the SQL Server?
This may have to be a separate inquiry, but I'm curious...If I changed the Unique Constraint to be a Unique Clustered Index, would that be sufficient for this table? Would I still need the Index on VisitDate? I've read that Clustered Indexes really shouldn't be composite which is why I didn't use this in the first place...
Thanks for any help you can offer!