0

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?

1 Answer 1

1

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.

2
  • 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, 2011 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. Aug 7, 2011 at 20:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .