How to make a composite key to be unique?

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.

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 PKfirst_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 PKstudent_id       INT FKclass_id         INT FKacademic_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 PKstudent_id       INT FKclass_id         INT FKacademic_year    INTclass_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.

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