Database problem--student registering for the same class twice..how to stop it Access 20007

I am making a mock database and I have a student table, student_class linking table, and a class table. How can I make it so that a student cannot be registered for the same class twice?

Example: StudentID 21 and classID 34 can be entered X number of times in the linking table.

The tables are des开发者_如何学编程igned 3NF, too.

In your student_class linking table (otherwise called a composite table), make both values, StudentID and ClassID, primary keys. So that table will have 2 columns that are composite primary keys and also foreign keys to their associated tables. Does that make sense?

A student_class table probably has at least two columns. One would be a foreign key to the primary key in student; the other would be a foreign key to the primary key in class. You need a primary key constraint on that pair of columns.

For example, if student_class had the two columns student_id and class_id . . .

• Open the table student_class in design view.
• Select both those columns.
• Click the key icon.

That should do it.

You could have a table that tells you which students are enrolled in which classes, named students_classes. If you make a composite primary key on this table containing student_id and class_id, then it should be impossible to have the database contain a student in the same class twice.

I should point out that you should not have an auto-increment ID primary key column on this table (I think Access likes to add this by default so if it did you may have to remove it). If you do, you could still end up with duplicates if all three are a part of the key. Example:

ID   student_id    class_id

113          1           2
114          1           2

Having the ID field still allows you have a student enrolled twice in the same class.