How to create a composite Primary Key on a Table

Question

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.

enter image description here

Thanks

Do you need this as a ___primary___ composite key, or simply as a ___unique___ composite key — Mark Baker, May 20 '14 at 07:59

score 2 · Accepted Answer · answered May 20 '14 at 07:59

2

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.

answered May 20 '14 at 07:59

Ravinder Reddy

23,692
6
52
82

score 0 · Answer 2 · answered May 20 '14 at 07:59

0

Try this

ALTER TABLE TABLE_NAME ADD unique index(StudentId,SemisterCourseId);

answered May 20 '14 at 07:59

Sadikhasan

18,365
21
80
122

A table can only have one `primary key`. OP said table has `'1 primary key on the table which is incremented by auto-generated number'`. So this is not going to work. – Ravinder Reddy May 20 '14 at 08:01
Ok. Good that you changed your solution. – Ravinder Reddy May 20 '14 at 11:51

How to create a composite Primary Key on a Table

2 Answers2