Each record in this table represents a unique set of dates in the project review cycle. Thus each record is assigned a datetypeID (1- initial review; 2 - continuing review, 3 - final review). My dilemma arises in that each projectID will have multiple date sets (M-1 relationship with project table) however the date type of "initial" can only be used once, but the other can be used many times.
My question is without using a (no duplicates) index on the DateTypeID field how can I make sure that each ProjectID has only one DateTypeID (1-Initial Review) record.
Originally posted by rtkracht
Wouldn't extending the key to include DateID, ProjectID and DateTypeID have the desired effect?
No, it wouldn't. Lets think of these three fields as a triad
(DateID, ProjectID, DateTypeID).
In your suggestion the triad (1,1,1) would be a unique value. I agree with this and that would be fine except.... (2,1,1) would also be unique. I am looking for a way to prevent the following secnario:
(3,1,1) and so on.
I only want the (?, X, X) combination to happen once. Thus an acceptable scenario would be:
(7,2,2) and so on. This is because the DateTypeID of "1" represents the inital review, and there can only be 1 intital review. However all other DateTypeID's can occur any number of times.