如何在表上创建复合主键[复制]

Question

This question already has an answer here:

• How do I specify unique constraint for multiple columns in MySQL? 11 answers

I have created a table, i have inserted some data as well. I have 1 primary key on the table which is incremented by auto-generated number. I have user_id and semester_course_id columns both are foreign_keys. I want to add composite key on 2 Columns user_id and semester_course_id .

So that 1 Student_id can register to 1 Course only. Semester_Course_id and student_id will repeat in the table, but together they have to appear one time only.

Kindly tell me how to add composite primary key.

Thanks

</div>

Answer 1

Add unique constraint on combination of both columns.

alter table table_name
  add constraint uk_sid_coursid
      unique key ( studentid, semistercourseid )

For this composite key to work as secondary primary key, define each of this field as not null.