INSERT IGNORE or Primary Key MySQL

Question

I have three tables in my ClassSelector DB:

students

student_id(P_key) | student_name | hometown

classes

class_id(P_key) | classname | description

student_x_class

student_id | class_id | classname

I am trying to enter students into classes, but prevent entering the same student twice in the same class. I tried INSERT IGNORE, but that blocked me from entering the same student in two different classes. I have no primary key in the student_x_class table. Could I student_id and class_id primary keys?

I guess the real question is how do I make a unique value for two columns(Student_id, class_id)?

You can make a *composite* PRIMARY (or UNIQUE if you don't it to be the primary) key. `ALTER TABLE student_x_class ADD PRIMARY KEY(student_id, class_id);` See: http://stackoverflow.com/q/8859353 — gen_Eric, Mar 02 '16 at 19:30

score 2 · Accepted Answer · answered Mar 02 '16 at 19:31

You can declare them to be either primary keys or unique keys:

create table student_x_class (
    student_id int not null,
    class_id int not null,
    primary key (student_id, class_id),
    constraint fk_sxc_student foreign key (student_id) references students(student_id),
    constraint fk_sxc_class foreign key (class_id) references classes(class_id)
);

You can also declare the combination to be unique instead.

INSERT IGNORE or Primary Key MySQL

1 Answers1