I see your point now, and you are right. The addition of the ID field in the ClassesTrainers table will allow for duplicates, which we do not want.
After watching a few other episodes, I found out what happened. Initially, the ClassesTrainers table had a composite primary key (PK) made up of the ClassID and the TrainerID. This ensured there were no duplicate entries in this table.
Then, in the Domain Integrity Part 3 episode, we found a problem when inserting new records in the SchClasses table. We could schedule any class with any trainer, regardless of the information in the ClassesTrainers table. There was a flaw in our design. To fix this, we changed the design of the ClassesTrainers table to include an ID field, and we set that to be the PK, and added the ID field as a foreign key to the SchClasses table.
This fixed the problem of scheduling classes, we could only select combinations of class/trainer that existed in the ClassesTrainers table. What we failed to realize, is that we could now duplicate information in the ClassesTrainers table.
Great catch on your part!
To solve this and make sure we cannot have duplicate data, we need to create a unique index that covers the combination of ClassID and TrainerID fields. This is what a primary key does, but we can only have one PK. A unique index will allow us to enforce our unique requirement, while still using the ID field as the PK.
To view existing indexes and create a new one, open the table in design view. In the design ribbon, select Indexes
Click in the first blank row under Index Name, and type a name for the new index, something like ClassTrainer. Then use the dropdown list in the Field Name column to select the ClassID field.
To create a composite index, we simply add another field to the same index name. In the next row, leave the index name blank, and use the Field Name dropdown list to select the TrainerID field.
Almost done. Now select the index name and you will see the properties of the new index. Notice Unique is listed as "No"
If you click where it says No, you will see it is a dropdown list. Change it to Yes.
That's it. Close the Indexes window and save the changes to the table. Switch to datasheet view and try to insert a duplicate record.
Now we can use the ID field as the primary key, and still ensure that the combination of Class and Trainer is unique!