How to make a composite key unique?

Question

I am doing a database of students in one school. Here is what I still have:

If you do not like to read the transition to the part "In short"

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

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

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

PS I apologize for the indescriptive header, but I call myself poorly

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

So, the student can be from class 7b and his number in the class is 5

Answer 1

Do not 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 . Thus, no two rows could have the same values ​​for these three columns. When you write that you do not want these three as a composite primary key, I am not sure if a complex unique unique key is better. If not, you will have to clarify when compound 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 is just the name I wrote from the initials of the key columns; use whatever name you want, as it doesn't really matter if you don't want to identify the key in some EXPLAIN output or similar.

Answer 2

I don’t quite understand why you need what you described, but I would look at the model as follows ...

Do you have students
- These are different objects, not components of other objects - They have their own properties; name, date of birth, etc.

You have classes
- This is a group of students
- Each academic year of the same "class" different students participate in it
- They also have their own properties; class, subclass, etc.

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

This will give me the following measurement 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 restriction on (grade_id, subgrade_id) , so only one class could be 7b .

Then you need to associate students with 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 be in only one class in any school year, you must set a unique limit on (student_id, academic_year) .

Alternatively, you can get the school year in the Class table. This would mean that you would repeat the same class for each year, but in some years, class 7g might not exist (for example, fewer students study this year).

Equally, you may have students who go from 7b to 7c in the middle of the year. In this case, the Class_Membership table may have a start_date field and, possibly, an end_date field.

However, none of this 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 functions and, possibly, additional functions. However, if necessary, you can 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 may also have a unique restriction on (academic_year, class_id, class_member_id) .

There is quite a bit of flexibility here, depending on your specific model of the real world and your specific needs. But hopefully this example is a good start for you; Size tables listing entities and a fact table (or tables) linking these entities together and / or further description of objects.

Answer 3

 alter table <TableName> add constraint <ConstraintName> unique(<col1>, <col2>) Modified the answer due to syntax mistakes. Mentioned above is correct.