up vote 0 down vote favorite
share [g+] share [fb]

I have two tables, students and class, which are joined by a students_classes junction table (with the student id and class id). How can I prevent identical entries from being entered into the Student_Classes table? For example, if the Student_Classes table has already got a row with ID class = 1 and ID student = 1 entry, how can I block an identical input?

link|improve this question
88% accept rate
feedback

1 Answer

up vote 1 down vote accepted

You need to use a compound unique index, one with two fields in it. In this case, the index would be on the two IDs, and should probably be made the primary key, too.

In the Access table designer, create your two fields as Number/Long Integer fields with no indexes and no default value. Then select both rows and hit the primary key toolbar icon (the one with the key). Performance-wise, you may choose to place a non-unique index on the second field in the compound index, but that will be used only on joins to that field.

link|improve this answer
Is there any difference between creating a compound index the way you described it (selecting the rows and clicking primary key icon) or by creating it in Design->Indexes window? – wowpatrick Aug 6 '11 at 10:06
Not in the results, but I think it's fewer steps the way I described it. And you don't have to know the "secret" of leaving the first column blank for the subsequent fields in the compound index. – David W. Fenton Aug 7 '11 at 20:29
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.