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

Southwick

I don't understand, are all students in a class garaunteed to have different grades? – Keeter

What will you do when students change class or, does this never happen? – Keeter

What is wrong with a student_id column? – Keeter

Are students in just 1 class, like a primary/junior/infant school? – Keeter

@Keeter I have edited my question to be more clear about those columns. And I want student_id to be a FK from another table which contains those 3 columns that serve as Composite key in it. If you still have questions ask – Southwick

In your example, what happens next accademic year? Will student 5 in class 7b move to class 8b? (or some other class?) – Candlepower

Your title says: "How to make a composite key to be unique?" All keys are unique, including composite keys. Please improve your title. Also, when you say: "... make a composite key from the 3 but I don't want that either", why you don't want it? – Douai

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.

Datnow

thanks I'll try that and I probably confused people with the nameings of grade, subgrade and id_class, but in my country the education system is different. – Southwick

OK I tried that but when I made all 3 columns UNIQUE I got an error for duplicate entries. I had many grades 3 but they had different subgrades so it treated them as separate unique keys. Also what is supplemental? – Southwick

Don't make all thre columns unique by themselves, but together: ALTER TABLE students ADD UNIQUE gsc (grades, subgrade, class_id). gsc is just a name, use whatever you like. Then duplicates only occur if all three are the same. I used the term supplementary as opposed to primary: A key defined using this command is not primary, but still unique. – Datnow

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.

Candlepower

As you queston the schema as it stands, I'll add my thoughts to it here. @Bosak, in my understanding the “subgrade” is only a letter to distinguish different classes in the same grade. So while grade and subgrade identify a class, a subgrade by itself isn't really an entity. You'll rarely be interested in “all classes with subgrade b”, and even less interested in storing common attributes for all those classes b, right? In that case, I'd drop the SubGrade table. – Datnow

Answer 3

alter table <TableName> add constraint <ConstraintName> unique(<col1>, <col2>)

Modified the answer due to syntax mistakes. Mentioned above is correct.

Tamtama