How to make a composite key to be unique?

Question

I am making a database of students in one school.Here is what I have so far:

If you don't like reading jump to the "In short" part

The problem is that I'm not happy with this design. I want the combination of grade, subgrade and id_class to be unique and to serve as a primary key for the students table. I can remove the student_id and make a composite key from the 3 but I don't want that either. Maybe I should make another table lets say combination_id where grade, subgrade and id_class are foreign keys and there is one extra column comb_id that serves as ID for the table. And all the columns will be Primary Keys. But the problem is that those 3 columns can still repeat because of that extra column (comb_id). For example I can have the same grade, subgrade and class_id but different comb_id which will make the row valid because of the composite key of the 4 columns of the table (combination_id).

In short I want students_id to remain the only primary key of the table but to be a foreign key to another table which is somehow unique combination of grades, subgrade and class_id.

If I was not clear enough ask in the comments below and thank you in advance.

PS I'm sorry for the indescriptive title but I'm bad at naming

EDIT 1: To be more clear: grade can be 1 to 12 subgrade can be a to j id_class can be 1 to 30 and it is your number in class

So a student can be from 7b class and his number in class - 5

Answer 1

Don't mix the concepts of unique keys and primary keys. You can very well add a unique key spanning the three columns grades, subgrade and class_id. That way, no two rows could have the same values for these three columns. As you write that you don't want to have these three as a composite primary key, I'm not sure whether a composite unique supplemental key would be any better. If not, you'll have to clarify when composite keys are acceptable.

To create a unique key, you can use the following SQL statement:

ALTER TABLE students ADD UNIQUE gsc (grades, subgrade, class_id);

The word gsc there is just a name I made up from the initials of the key columns; use whatever name you want, as it hardly matters unless you want to identify the key in some EXPLAIN output or similar.

Answer 2

I'm not totally clear on why you want what you have described, but I would look at the model in the following way...

You have Students
- They are distinct entities, not a composite of other entities
- They have their own properties; name, date of birth, etc

You have classes
- These are groups of students
- Each accademic year the same "class" has different students in it
- They also have their own properties; grade, sub-grade, etc

You have an extra property in your model that I would not normally use
- If a class has 20 students, each of them is identified with a secondary id from 1 to 20

This would give me the following Dimension tables

Student                  Class                     Grade              SubGrade
-----------------------  ------------------------  -----------------  -----------------
id          INT PK       id           INT PK       id    INT PK       id    INT PK
first_name  VARCHAR(45)  name         VARCHAR(45)  name  VARCHAR(45)  name  VARCHAR(45)
last_name   VARCHAR(45)  grade_id     INT FK       desc  VARCHAR(45)  desc  VARCHAR(45)
etc, etc                 subgrade_id  INT FK       etc, etc           etc, etc

The Class table would have a unique constraint on (grade_id, subgrade_id) so that only one class could ever be 7b.

Then you need to relate the students to their classes using a fact table...

Class_Membership
-----------------------
id               INT PK
student_id       INT FK
class_id         INT FK
academic_year    INT

If a student should only ever be in one class in any academic year, you would put a unique constraint on (student_id, academic_year).

Alternatively, you could have the academic year in the Class table. This would mean that you would have the same class repeated for every year, but that in some years class 7g may not exist (as there are less students that year, for example).

Equally, you could have students who move from 7b to 7c mid-year. In which case the Class_Membership table could have a start_date field and possibly an end_date field.

None of that, however, directly creates the id_class field (1-20 for a class with 20 students). Personally I would not have such a field, the id field from the Class_Membership table can serve most of the same functionality, and probably additional functionality. Where it is necessary, however, you could simply add it to the Class_Membership table...

Class_Membership
-----------------------
id               INT PK
student_id       INT FK
class_id         INT FK
academic_year    INT
class_member_id  INT

Then you could also have a unique constraint on (academic_year, class_id, class_member_id).

There is quite a lot of flexibility here, depending on your exact real-world-model and your particular needs. But hopefully this example is a good start for you; Dimension tables listing Entities, and a Fact table (or tables) relating these entities together and/or further describing the Entities.